2

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.

8
  • I'm trying to make sense of your original post alongside this one. It looks like you want the LOCATION column to be the one sorted as unique, and the row for each LOCATION is built out using the values in UNIT #?
    – Peter
    Jun 9, 2017 at 20:10
  • Yes, that is correct, the initial sample data I posted was just proof of concepts. I want the location unique and the unit numbers built out
    – J King
    Jun 9, 2017 at 20:11
  • 1
    Try this formula: =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 the A2 value at the end.
    – Peter
    Jun 9, 2017 at 20:14
  • 1
    @Peter PERFECT! a big digital high five for you
    – J King
    Jun 9, 2017 at 20:28
  • Please don't include the solution in your question. Your question post should only contain your question. If an answer worked for you, please "accept" it. If you found the solution on your own, please answer your own question and answer it. Either way this acts as a sign post for future readers who have the same issue.
    – ale
    Jun 9, 2017 at 20:32

2 Answers 2

3

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

2
  • Thanks Peter! I'm almost there, please see my edit for details
    – J King
    Jun 9, 2017 at 16:27
  • Thanks for the steps, sadly I discovered this after doing it by hand but now I can reproduce in a couple of minutes what took me half an hour, because you explained how to use the sheets tools together :) May 2, 2019 at 9:28
2

Also two steps, assuming 256 is in B2:

  1. in C2: =COUNTIF(A$1:A2,A2) copied down to suit

  2. in D1:

    =query(A1:C8,"select A, SUM(B) group by A pivot C") 
    
1
  • Thanks pnuts, I can get the first formula to work like: =COUNTIF('HOURS & MILEAGE LOG'!D$7:D8,'HOURS & MILEAGE LOG'!D8) but the second one I can't get to work, see my edit for the actual source sheet structure. I tried this: =query('HOURS & MILEAGE LOG'D7:A8,"select D, SUM(B) group by D pivot A") but it did not work
    – J King
    Jun 9, 2017 at 16:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.