Query, Array, and sort functions in same formula

Question

I have an ARRAY that is pulling data from a form, that is then using a QUERY to combine rows with the same data in column 1 that I now need to sort alphabetically by the data in column 2. I can't figure out how to integrate that aspect into the given formula.

Also, in the formula, I found online (that was super helpful!), I am now getting the first row of data stating "max" before the rest of the data and a blank row directly after it... But I can't get rid of either of them!

Any help would be so so so appreciated.

This is the formula I am currently working with (it's super long and I can't figure out how to highlight parts- I'm sorry! It's the last clause from "SELECT" onwards that's causing weird formatting though)

=QUERY({ARRAYFORMULA({
 'Raw Data'!C2:C1501&" "&'Raw Data'!D2:D1501,
 'Raw Data'!E2:E1501,
 'Raw Data'!H2:H1501,
 'Raw Data'!J2:J1501&'Raw Data'!K2:K1501&'Raw Data'!L2:L1501&'Raw Data'!M2:M1501&'Raw Data'!AK2:AK1501&'Raw Data'!AL2:AL1501&'Raw Data'!AM2:AM1501&'Raw Data'!AN2:AN1501&'Raw Data'!BF2:BF1501&'Raw Data'!BG2:BG1501&'Raw Data'!BH2:BH1501&'Raw Data'!BI2:BI1501,
 'Raw Data'!N2:N1501&'Raw Data'!Q2:Q1501&'Raw Data'!AO2:AO1501,
 'Raw Data'!P2:P1501&'Raw Data'!R2:R1501&'Raw Data'!AP2:AP1501,
 'Raw Data'!S2:S1501&'Raw Data'!T2:T1501&'Raw Data'!U2:U1535&'Raw Data'!V2:V1535&'Raw Data'!AK2:AK1535&'Raw Data'!AL2:AL1535&'Raw Data'!AM2:AM1535&'Raw Data'!AN2:AN1535&'Raw Data'!BF2:BF1535&'Raw Data'!BG2:BG1535&'Raw Data'!BH2:BH1501&'Raw Data'!BI2:BI1501,
 'Raw Data'!W2:W1501&'Raw Data'!Z2:Z1501&'Raw Data'!AV2:AV1501,
 'Raw Data'!Y2:Y1501&'Raw Data'!AA2:AA1501&'Raw Data'!AW2:AW1501,
 'Raw Data'!AB2:AB1501&'Raw Data'!AC2:AC1501&'Raw Data'!AD2:AD1501&'Raw Data'!AY2:AY1501&'Raw Data'!AZ2:AZ1501&'Raw Data'!BA2:BA1501&'Raw Data'!BN2:BN1501&'Raw Data'!BO2:BO1501&'Raw Data'!BP2:BP1501,
 'Raw Data'!AE2:AE1501&'Raw Data'!AH2:AH1501&'Raw Data'!BB2:BB1501,
 'Raw Data'!AG2:AG1501&'Raw Data'!AI2:AI1501&'Raw Data'!BC2:BC1501,
 'Raw Data'!CG2:CG1501,
 'Raw Data'!G2:G1501,
 'Raw Data'!BY2:BY1501&'Raw Data'!CA2:CA1501&'Raw Data'!CC2:CC1501})},
 "SELECT Col1,"&ArrayFormula(join(", ","Max(Col"&column(B1:O1)&")"))&"group by Col1",1)

Here's a copy of the sheet. It's the "For Advisor" tab that has the formula: docs.google.com/spreadsheets/d/… — Kristen
I see you sorted it with SORT... what else you want to do with it? — player0
Yes! Sorted that one out! Hoping to get rid of "max" in the first row of data? — Kristen
Sorry! The first actual row of data (before the SORT function pushes it down) is row 9. It has added "max" to each cell in that row — Kristen
So that part I did find online, so also find it super hard to understand (I'm glad I'm not alone in that). The form is filled out every few weeks to log in people fall, winter, and spring commitments. Instead of having 3 rows for each person, that formula is meant to pick all the rows with the person's name and amalgamate all the data into a single row. — Kristen

player0 player0 · Accepted Answer · 2019-04-04T02:48:12

=SORT(QUERY(QUERY({ARRAYFORMULA({
 'Raw Data'!C2:C&" "&'Raw Data'!D2:D,
 'Raw Data'!E2:E,
 'Raw Data'!H2:H,
 'Raw Data'!J2:J&'Raw Data'!K2:K&'Raw Data'!L2:L&'Raw Data'!M2:M&'Raw Data'!AK2:AK&'Raw Data'!AL2:AL&'Raw Data'!AM2:AM&'Raw Data'!AN2:AN&'Raw Data'!BF2:BF&'Raw Data'!BG2:BG&'Raw Data'!BH2:BH&'Raw Data'!BI2:BI,
 'Raw Data'!N2:N&'Raw Data'!Q2:Q&'Raw Data'!AO2:AO,
 'Raw Data'!P2:P&'Raw Data'!R2:R&'Raw Data'!AP2:AP,
 'Raw Data'!S2:S&'Raw Data'!T2:T&'Raw Data'!U2:U&'Raw Data'!V2:V&'Raw Data'!AK2:AK&'Raw Data'!AL2:AL&'Raw Data'!AM2:AM&'Raw Data'!AN2:AN&'Raw Data'!BF2:BF&'Raw Data'!BG2:BG&'Raw Data'!BH2:BH&'Raw Data'!BI2:BI,
 'Raw Data'!W2:W&'Raw Data'!Z2:Z&'Raw Data'!AV2:AV,
 'Raw Data'!Y2:Y&'Raw Data'!AA2:AA&'Raw Data'!AW2:AW,
 'Raw Data'!AB2:AB&'Raw Data'!AC2:AC&'Raw Data'!AD2:AD&'Raw Data'!AY2:AY&'Raw Data'!AZ2:AZ&'Raw Data'!BA2:BA&'Raw Data'!BN2:BN&'Raw Data'!BO2:BO&'Raw Data'!BP2:BP,
 'Raw Data'!AE2:AE&'Raw Data'!AH2:AH&'Raw Data'!BB2:BB,
 'Raw Data'!AG2:AG&'Raw Data'!AI2:AI&'Raw Data'!BC2:BC,
 'Raw Data'!CJ2:CJ,
 'Raw Data'!G2:G,
 'Raw Data'!BY2:BY&'Raw Data'!CA2:CA&'Raw Data'!CC2:CC})},
 "select Col1,"&JOIN(", ", "max(Col"&COLUMN(B1:O1)&")")&"
  group by Col1", 0),
 "where Col1 is not null"), 2, 1, 1, 1)

Query, Array, and sort functions in same formula

1 Answers