In Excel, How do I turn Table A into Table B?

January 18, 2022 6:21 PM

In Excel, how do I turn Table A into Table B? I'm pretty sure it's something to do with PIvot tables but I can't figure it out (google sheet)

Table A
Name Clothing Color
John shorts blue
john hat red
frank shorts red
bob hat blue


Table B
Name Shorts Hat
john blue red
frank red
bob blue

posted by cacofonie to Computers & Internet (4 answers total)

I think what you want is to do with the look up function, maybe?
posted by freethefeet at 6:54 PM on January 18, 2022

vlookup. There are lots of helpful walkthroughs and it's very useful. The newer m365 excel has some different ways to do that.

Pivot would also work - you make your source table a table (format as table) and then you go insert a pivot table in a new worksheet and then go nuts moving fields into rows, filters, columns etc. Pivot is very handy and surprisingly easy once you get the hang of it. You can copy and paste a pivot report set up the way you want into a new worksheet if you paste as value.
posted by dorothyisunderwood at 7:18 PM on January 18, 2022

If you're happy with Power Query in Excel, you could unpivot the Clothing column using Color as the values
posted by Faff at 10:19 PM on January 18, 2022

This assumes your data is clean:

https://docs.google.com/spreadsheets/d/ 1Hwm2j-Ytrywd_Kd22LfedftIHf74zBs6e6obIgAoLIY /edit#gid=0
posted by casual observer at 1:10 AM on January 19, 2022