excelgoogle-sheets

Transform transactional text data to columns in Excel or Google Sheets

I have a table of 40,000+ historical performance rating records that I need to pivot into columns using either excel or google sheets. In SQL I'd write a case statement but this info isn't in my data warehouse.

ID Cycle Rating
A 1 ME
A 2 MM
B 1 EE
B 2 ME

I'd like the table to look like this

ID Cycle 1 Cycle 2
A ME MM
B EE ME

I tried pivot tables but they are intended for aggregation. I tried Query in Google Sheets but couldn't figure it out. I suppose I could filter and copy/paste but I'm hoping for a more programmatic solution.

Solution

  • In Google Sheets you can indeed use the QUERY function as follows:

    =LET(data, HSTACK(A1:A5, ARRAYFORMULA(B1&" "&B1:B5), C1:C5), QUERY(data, "select Col1, min(Col3) group by Col1 pivot Col2", 1))

    The LET part, before the actual query, is just there to prepend the word "Cycle" to the cycle numbers in the headers. Since you need to use an aggregate function, I've chosen (rather arbitrarily1) min().

    Google Sheets output.

    1 This assumes that there aren't multiples row with identical ID and cycle but different rating, as in your example data.

    EDIT - If you want to use whole columns as ranges (e.g. A1:A), you need to add the condition where A is not null to the query; otherwise there would be an empty column and an empty row. The formula becomes:

    =LET(data, HSTACK(A1:A, ARRAYFORMULA(B1&" "&B1:B), C1:C), QUERY(data, "select Col1, min(Col3) where Col1 is not null group by Col1 pivot Col2", 1))

    References