I have a Google sheet with values like:
Column 1 Column 2
----------|--------------
A 256
A 271
B 270
C 262
A 266
C 258
D 257
I want to transform the data into the following:
Column 1 Column 2 Column 3 Column 4 Column 5
-----------|--------------|-------------|--------------|------------
A 256 271 266
B 270
C 262 258
D 257
I want to group the distinct values of Column 1 and then for each value add them to columns for that value.
I don't have a lot of experience with pivot tables but I could not get it to work because the values were looking for aggregates of some kind like SUM or COUNT. I don't want to aggregate, I want to list in columns all the values found for the group.
I hope this makes sense.
Anyone have any ideas how to do this. I am comfortable with APP Scripts but was hoping there was a quicker way than dozens of lines of code building a custom sheet.
EDIT #1: Peter's Answer
hi peter, thanks for the answer, the following works great for the first step
=SORT(unique('HOURS & MILEAGE LOG'!D7:D))
but the second step is not working for me. It is just copying the same values for every row. In the real sheet the unique column is actually D and it starts at D:7 and the values to be filled out across in the row are from column A:7 and down: see the following screen shot:
so I switched the transpose formula to:
=transpose(filter('HOURS & MILEAGE LOG'!A:A,'HOURS & MILEAGE LOG'!D:D=D8))
the values you see are header rows for the type of vehicle, CHIEF, PUMP, TANKER etc...
I need the unit numbers between these rows to fill out to the right.
=transpose(filter('HOURS & MILEAGE LOG'!A:A,'HOURS & MILEAGE LOG'!D:D=A2))
I think the comparison at the end might be causing the issue. If it works for the top column, use the formula dragger to apply it to the cells below it and automatically update theA2
value at the end.