Spreadsheet with relationships

0
votes

I have to work with data CSV file. They look like this

sample It represents products with options/cars etc. at the web-store.

It has a lot of columns with duplicated values and in my work in often need to copy some part of this data to another sheet, deduplicate it, edit and then paste it back by matching it for one of the columns that were untouched. More this purpose I'm using Ablebits Excel suit.

Is it possible by any excel function to automate this process or maybe there is some other software that could handle this? Something not so complicated as relational databases like Access, but something close to spreadsheet editor with relationships

I already tried Power Query in Excel and Power Bi, but they seem to be more analytics tools and not the data edit

2nd edition: Data has a layer structure with duplicates.

Title1|Part number 1|Car1
Title1|Part number 1|Car2
Title2|Part number 2|Option1
Title2|Part number 3|Option2

I want to have opportunity to:

  • Edit values that duplicate without using "Replace All" or at least have more flexible "Find&Replace".
  • Extract columns with deduplicating them and saving a reference to the place they were taken. So if you edit some data there it was changed in the 1st place. For example, I have titles(a lot of titles) but need to edit it. Instead of copying it with some id to reference it I want to open it like they appears in filters, edit it, confirm and get it edited in all column
1
excelspreadsheetpowerbipowerqueryimport-from-csv
For us to answer this question, you need to be more specific. Please explain what part do you copy to another sheet, how do you deduplicate data, and what relation do you use to join it back. In short, using plain Excel formulas seem to be too complicated and ineffective, PowerQuery is much better tool for this task. – Eugene
Also, please include (minimal) data for a repro in the question, don't expect us to go to an unknown dropbox link. See "minimal reproducible example" for guidance. – Jeroen
You may use Google.Docs if you want to post table data, but any code should be posted here. – Eugene
@Jeroen Although off-topic, your dishwasher experiment blew my mind away! :)) Great approach! – Eugene
PS. I do try to practice what I preach, if you're looking for examples. – Jeroen

1 Answers

0
votes

I would use Power Query (aka Get & Transform on the Data ribbon in Excel 2016). The only limitation I see with what you want to do is that Power Query will deliver a new Excel Table with the output of a Query - it can't update existing cells.

If you can get past that, Power Query is very flexible, easy to learn (WYSIWYG query editor), scales well and is integrated with other Microsoft products (as well as Power BI, there is integration with SQL Server Analysis Services in preview and hopefully SQL Server Integration Services one day).