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

google sheetsgoogle-apps-scriptpivot

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:

enter image description here

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:enter image description here

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.

Best Answer

You can accomplish this in two steps:

  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:

Google Sheets Formulas

Related Topic