How can I (in Google Sheets) obtain the outcome I'm after here?

Assuming starting with:

enter image description here

want to get:

enter image description here


This should do it:

=ArrayFormula(SPLIT(QUERY(FLATTEN(IF(FILTER(B2:E,A2:A<>"")="",,FILTER(B2:E,A2:A<>"")&"|"&FILTER(A2:A,A2:A<>""))),"Select * Where Col1 Is Not Null Label Col1 'Name|Number'"),"|"))

How It Works:

I applied FILTER to the ranges to keep things focused on only the area you have data in, but you could have done without those.

The IF says (in plain English), "If a cell in the range B2:E is blank, leave it that way; otherwise, concatenate whatever is there with a pipe symbol followed by the number in Column A of that row."

FLATTEN is an as-yet-unofficial Google Sheets functions that will take all of those columns of results and "flatten" them into one column.

QUERY weeds out the blanks. FLATTEN leaves only one column (Col1) to process, and we keep only those where the cell "Is Not Null." This also seemed a good time to add the header label (also concatenated as the other data for now).

SPLIT just splits everything — concatenated headers and number-name combos — at the pipe symbol to give back the two columns.

ArrayFormula (...) is necessary because we are processing several arrays here.

  • @serenesat, why did you feel it was necessary to edit my post (e.g., remove the @ to the OP, change the capitalization of "HOW IT WORKS," take away formula formatting on my formula, etc.)?
    – Erik Tyler
    Dec 9 '20 at 4:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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