Transpose names across multiple columns/rows into a single column in Google Sheet with twist

Question

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

Assuming starting with:

want to get:

Answer 1

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.