Google Sheets – How to Group Column Data and Display Values in a Row

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.

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))
``````

and this is the result:

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.

1. Create a new sheet `Sheet2` and use the following formula for A1 (or wherever you want the column to begin): `=unique(Sheet1!A:A)`. This will give you a vertical column of simply A, B, C, and D as the unique values in `Sheet1`'s A column.
2. Combine transpose and filter in the cell adjacent to each cell in `Sheet2` as follows: `=transpose(filter(Sheet1!B:B,Sheet1!A:A=A1))`. Here, you are filtering the second column of your `Sheet1` according to whether or not the letter next to in `Sheet1` matches the latter next to it in `Sheet2`. This normally prints vertically, so you use transpose to make it print horizontally, i.e. in the same row as you show above. Then, drag the formula down to apply it to each successive row.
The formulas in `Sheet2` end up looking like this: