Forum Discussion
Help moving data and matching to correct columns and rows.
I am trying to convert product data so I can upload it into a website in its specific format. To do so I need to change the layout of the current file so I can move the data. So I need to put the data from D into the specific columns that I made from C and it must match the part numbers. There is 298 Unique attributes from column C that I made into each of their own columns. I need to match the correlating part numbers and take the data from D to its correct part number location and column. I am lost completely on this.
So IF E2=A2 and AU1=C2, return D2, then must count again and IF E2=A3 and BS1=C3 return D3. Any help would be awesome.
turboedbird Are you familiar with Power Query (PQ)?
It would take only a few steps (connect to the csv file, keep three columns, promote headers, do some sorting, data typing and cleaning-up and then pivot the attribute column). Now, if you are not familiar with PQ, this will mean absolutely nothing to you.
I've done all that but noticed that, for instance, part number 300104ERL has one attribute called "Compatible Hose" with 5 different values on 5 different rows. The pivot step mentioned above couldn't handle this, so an extra step had done to group such items. But then the five different hose types are merged into one cell. Not sure if that is a problem for you or not.
The end result is a table with 4203 rows and 298 columns. File attached, though you will not be able to connect to the source file as it was linked to a location on my computer. Now, if you do know PQ, you'll be able to fix that yourself.
6 Replies
turboedbird Are you familiar with Power Query (PQ)?
It would take only a few steps (connect to the csv file, keep three columns, promote headers, do some sorting, data typing and cleaning-up and then pivot the attribute column). Now, if you are not familiar with PQ, this will mean absolutely nothing to you.
I've done all that but noticed that, for instance, part number 300104ERL has one attribute called "Compatible Hose" with 5 different values on 5 different rows. The pivot step mentioned above couldn't handle this, so an extra step had done to group such items. But then the five different hose types are merged into one cell. Not sure if that is a problem for you or not.
The end result is a table with 4203 rows and 298 columns. File attached, though you will not be able to connect to the source file as it was linked to a location on my computer. Now, if you do know PQ, you'll be able to fix that yourself.
- Thank you for the work and information it is greatly appreciated. I spent a lot of hours trying different indexes, VLOOKUPS, MATCHES and just getting stuck in the same thing.
I have never heard of PQ, but I will be downloading it now and trying to figure it out. Since this was only for one brand I will still need to learn to do this for all of the other brands that will be imported. So looking at the steps you took, I will need to:
1. connect to the csv file
2. keep three columns (Partnumber, Attribute, Value) I am assuming these three.
3. promote headers (Promote Attributes)?
4. do some sorting (What did you sort on?)
5. data typing and cleaning-up (Not sure if this is just making it look pretty or something else?)
6. then pivot the attribute column (When you pivot the column does the data follow at that point?)
Extra step for grouping multiple items with the same attribute (Does PQ allow you to group without losing data?)
I think I can figure most of those out with some googling but may need some clarification on the data typing and cleaning-up? Would it be possible for you to elaborate on these for me? Again thank you for all the help it is greatly appreciated.turboedbird Difficult to explain all of it in text here, but there are many resources on-line where you can learn the basics. For instance here:
https://exceloffthegrid.com/power-query-introduction/
If you are on a Windows version of Excel (2016 or thereafter), PQ is included. In more recent version you'll find the tools on the Data ribbon under "Get and transform data".
Once you have mastered the basics, you'll be able to follow the steps applied. If nor, com back here.
