SOLVED

Help moving data and matching to correct columns and rows.

%3CLINGO-SUB%20id%3D%22lingo-sub-2270930%22%20slang%3D%22en-US%22%3EHelp%20moving%20data%20and%20matching%20to%20correct%20columns%20and%20rows.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2270930%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20convert%20product%20data%20so%20I%20can%20upload%20it%20into%20a%20website%20in%20its%20specific%20format.%20To%20do%20so%20I%20need%20to%20change%20the%20layout%20of%20the%20current%20file%20so%20I%20can%20move%20the%20data.%20So%20I%20need%20to%20put%20the%20data%20from%20D%20into%20the%20specific%20columns%20that%20I%20made%20from%20C%20and%20it%20must%20match%20the%20part%20numbers.%20There%20is%20298%20Unique%20attributes%20from%20column%20C%20that%20I%20made%20into%20each%20of%20their%20own%20columns.%20I%20need%20to%20match%20the%20correlating%20part%20numbers%20and%20take%20the%20data%20from%20D%20to%20its%20correct%20part%20number%20location%20and%20column.%26nbsp%3B%20I%20am%20lost%20completely%20on%20this.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20IF%20E2%3DA2%20and%20AU1%3DC2%2C%20return%20D2%2C%20then%20must%20count%20again%20and%20IF%20E2%3DA3%20and%20BS1%3DC3%20return%20D3.%26nbsp%3B%20Any%20help%20would%20be%20awesome.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22turboedbird_0-1618344807247.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F272603i7F02C1F42ED7F5F7%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22turboedbird_0-1618344807247.png%22%20alt%3D%22turboedbird_0-1618344807247.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2270930%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2271859%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20moving%20data%20and%20matching%20to%20correct%20columns%20and%20rows.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2271859%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1025115%22%20target%3D%22_blank%22%3E%40turboedbird%3C%2FA%3E%26nbsp%3BAre%20you%20familiar%20with%20Power%20Query%20(PQ)%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%20take%20only%20a%20few%20steps%20(connect%20to%20the%20csv%20file%2C%20keep%20three%20columns%2C%20promote%20headers%2C%20do%20some%20sorting%2C%20data%20typing%20and%20cleaning-up%20and%20then%20pivot%20the%20attribute%20column).%20Now%2C%20if%20you%20are%20not%20familiar%20with%20PQ%2C%20this%20will%20mean%20absolutely%20nothing%20to%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20done%20all%20that%20but%20noticed%20that%2C%20for%20instance%2C%20part%20number%20300104ERL%20has%20one%20attribute%20called%20%22Compatible%20Hose%22%20with%205%20different%20values%20on%205%20different%20rows.%20The%20pivot%20step%20mentioned%20above%20couldn't%20handle%20this%2C%20so%20an%20extra%20step%20had%20done%20to%20group%20such%20items.%20But%20then%20the%20five%20different%20hose%20types%20are%20merged%20into%20one%20cell.%20Not%20sure%20if%20that%20is%20a%20problem%20for%20you%20or%20not.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20end%20result%20is%20a%20table%20with%204203%20rows%20and%20298%20columns.%20File%20attached%2C%20though%20you%20will%20not%20be%20able%20to%20connect%20to%20the%20source%20file%20as%20it%20was%20linked%20to%20a%20location%20on%20my%20computer.%20Now%2C%20if%20you%20do%20know%20PQ%2C%20you'll%20be%20able%20to%20fix%20that%20yourself.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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_0-1618344807247.png

 

6 Replies
best response confirmed by turboedbird (New Contributor)
Solution

@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.

@Riny_van_Eekelen Hi, I think I have a similar issue, but on a smaller scale. The answer may lie in Power BI, which someone else is looking into, but for now, I am getting csv files from data from our client database, and even if it's cleaned up in Power Bi, when we export out, it appears like table on left in pic below. 

 

We want it to end up like Table on right in pic. I've done a little playing with PQ, but can't get it to get data to stay married properly, without just ending up with hundreds of columns and the questions repeated etc

(This isn't the actual data, but the set up is identical.) Have attached this spreadsheet.

 

Any help very much appreciated. 

 

Screenshot 2021-04-26 132056.png

@Davidm54 
Can offer you two solutions. Choose the one you like best.
Power Query: Connect to the blue table, pivot the Question column with Answer as the values, without aggregation. Load to Excel.


Power Pivot: Add the blue table to the Data Model and create a DAX measure for the Answer column like:

=CONCATENATEX( Table1, Table1[Answer] )

Create a pivot valve with Questions in the column field, Name in the row field and the newly created measure in the value field.

 

Both solutions are included in the attached file.

Thank you so much. I'm only weeks into using PQ consistently, so there's still stuff confusing me about it, this not only solves problem, but also shows me I was looking in right spot, I just hadn't quite sussed what the options effected. Wins all round. thanks again.