Reputation: 133
Ok, so I have some data that I want to convert from multiple rows to multiple columns.
My input data looks loosely like this -
+----------+----------------+-----------------+
| SKU | Attribute Name | Attribute Value |
+----------+----------------+-----------------+
| Product1 | Colour | Black |
| Product1 | Size | Large |
| Product1 | Height | 20cm |
| Product1 | Width | 40cm |
| Product2 | Colour | Red |
| Product2 | Width | 30cm |
| Product2 | Size | Large |
| Product3 | Height | 25cm |
| Product3 | Width | 30cm |
| Product3 | Length | 90cm |
| Product3 | Weight | 5kg |
| Product3 | Size | Large |
| Product3 | Colour | Blue |
+----------+----------------+-----------------+
What I want to achieve is an output like this -
+----------+--------+--------+--------+-------+--------+-------+
| SKU | Colour | Height | Length | Size | Weight | Width |
+----------+--------+--------+--------+-------+--------+-------+
| Product1 | Black | 20cm | | Large | | 40cm |
| Product2 | Red | | | Large | | 30cm |
| Product3 | Blue | 25cm | 90cm | Large | 5kg | 30cm |
+----------+--------+--------+--------+-------+--------+-------+
I've tried Pivot tables, but you can only return numeric values, rather than the text values I'm looking for.
I know I could probably achieve it using a number of step looking up values and filling them, but I feel like there should be a more simplistic way to achieve this. Maybe it's something better achieved in database rather than a spreadsheet.
Any help would be very much appreciated.
Upvotes: 1
Views: 2100
Reputation: 96781
This assumes that the data is in columns A through C:
Sub croupier()
Dim i As Long, N As Long, vA As String, vB As String, vC As String
Dim rw As Long, cl As Long
' setup column headers
Columns(2).SpecialCells(2).Offset(1).Copy Range("D1")
Columns(4).RemoveDuplicates Columns:=1, Header:=xlNo
Columns(4).SpecialCells(2).Copy
Range("E1").PasteSpecial Transpose:=True
Columns(4).SpecialCells(2).Clear
' setup row headers
Columns(1).SpecialCells(2).Copy Range("D1")
Columns(4).RemoveDuplicates Columns:=1, Header:=xlYes
' deal the data
N = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To N
vA = Cells(i, 1)
vB = Cells(i, 2)
vC = Cells(i, 3)
cl = Rows(1).Find(what:=vB, after:=Range("A1")).Column
rw = Columns(4).Find(what:=vA, after:=Range("D1")).Row
Cells(rw, cl) = vC
Next i
End Sub
Upvotes: 0
Reputation: 84465
You can do this in ̶5̶ ̶s̶t̶e̶p̶s̶ 4 steps with Powerquery. This is in-built for 2016 and a free add-in from Microsoft from 2013 on wards ( or 2010 Professional Plus with Software Assurance). See info https://www.microsoft.com/en-gb/download/details.aspx?id=39379
The advantage is you can easily add rows to the source and simply refresh the query.
1) You select any cell in the range, then in 2016 Get & Transform tab, earlier version use the Powerquery tab, select data from table. A window will pop up with your range of data in:
2) Transform > Pivot column > Attribute Name column for Attribute Value in Values Column (used advanced options to select "Don't aggregate")
3) Drag columns around to desired arrangement
4) Home > Close and load to sheet
Here is a version without the column re-ordering
Edit:
Thanks to @Ron Rosenfeld for reminding me that truly null values don't need replacing with blanks as they will appear as blanks when written to the sheet.
So this step was removed:
4) Highlight columns to replace nulls in and go to transform > replace values > and
Value to Find: null
Replace With:
Upvotes: 3
Reputation: 1584
You could do this using a helper column and then match it using index + match. Not as simple as you thought, but does work.
1) Add helper column to your data (call it 'Helper'). =concat(SKU,'Attribute Name')
2) Use a pivot to get a unique list of SKUs in the rows so that it's easy to update once the data changes. (I'm assuming this is in column A and values start at row 4).
3) Use another pivot to get a unique list of Attributes in the columns next to the other pivot. Then you have the structure of your results. (I'm assuming the first value is in B3).
4) Index match the values of the table =index('Attribute Value', match(concat($A4,B$3),'Helper',0))
Note though that this only works when each combination of SKU and Attribute is unique.
Upvotes: 0