Merge rows by ID in excel

Question

I've got a set of columns like this in excel:

ID                                      sizeAndColourAndSku_Value
2ff0d3d7-8c18-4386-83be-1b2485fb4ea9    Select size
2ff0d3d7-8c18-4386-83be-1b2485fb4ea9    Extra Small - Available (only 1 left)
2ff0d3d7-8c18-4386-83be-1b2485fb4ea9    Small - Available (only 2 left)
2ff0d3d7-8c18-4386-83be-1b2485fb4ea9    Medium - Available (only 2 left)
2ff0d3d7-8c18-4386-83be-1b2485fb4ea9    Large - Available (only 1 left)
4697ce05-2e50-4e67-85d5-ad733e15ae95    Select size
4697ce05-2e50-4e67-85d5-ad733e15ae95    6 - Available (only 2 left)
4697ce05-2e50-4e67-85d5-ad733e15ae95    8 - Available (only 4 left)
4697ce05-2e50-4e67-85d5-ad733e15ae95    10 - Available (only 6 left)
4697ce05-2e50-4e67-85d5-ad733e15ae95    12 - Available (only 6 left)
4697ce05-2e50-4e67-85d5-ad733e15ae95    14 - Available (only 4 left)
4697ce05-2e50-4e67-85d5-ad733e15ae95    16 - Available (only 2 left)
4697ce05-2e50-4e67-85d5-ad733e15ae95    Select size

I need to combine merge them by their ID so the results become like this:

2ff0d3d7-8c18-4386-83be-1b2485fb4ea9   Extra Small  Small  Medium  Large
4697ce05-2e50-4e67-85d5-ad733e15ae95   6      8      10     12      14

I read here that the quickest and easiest way to do this is with a pivot table in excel

http://www.excelbanter.com/showthread.php?t=235727

However I'm using excel 2010 and the method they've provided doesn't really work, anyone able to help me out with this?

Thanks

Answer 1

There is already an answer to this somewhere on SE (I wrote it but can't find it!) however:

1. Parse your sizeAndColourAndSku_Value column with Text to Columns and - as the delimiter.
2. Delete rows with Select size.
3. Assuming the Extra Small part of Extra Small- Available (only 1 left) is now in B2 put:

=IF(COLUMN()<COUNTIF($A:$A,$A2)+3,IF($A2=$A3,INDIRECT("$B"&ROW()+COLUMN()-3),""),"")

in C2 and copy across and down to suit.

4. Select entire sheet and Paste Special Values over the top.
5. In B2 put: =A1=A2 and copy down to suit.
6. Filter ColumnB for TRUE and delete visible rows.
7. Delete ColumnB.

Edit to add image:

Answer 2

If you pivot on this data range and apply the ID field to 'Row Labels' and the sizeAndColourAndSku_Value field to both 'Column Labels' and 'Values' you will get something similar, except it will give you a count of the occurrences rather than the values themselves.