Column headers to rows

Question

I am trying to convert data in Google Sheets that looks like this:

  A.  B.  C
1
2
3

to this:

A 1
A 2
A 3
B 1
B 2
B 3
C 1
C 2 
C 3

And I am trying to make it dynamic such that if you add rows to the first table, the second table gets updated as well. How can i do this? Preferably using a formula instead of Google Apps Script.

Answer 1

Or, as an alternative

=ARRAYFORMULA(SPLIT(FLATTEN(FILTER(Blad2!B1:1, LEN(Blad2!B1:1))&"_"&FILTER(Blad2!A2:A, LEN(Blad2!A2:A))), "_"))

or

=ARRAYFORMULA(FLATTEN(FILTER(Blad2!B1:1, LEN(Blad2!B1:1))&FILTER(Blad2!A2:A, LEN(Blad2!A2:A))))