1

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.

Sapppppp
  • 21
  • 4
  • share a copy of your sheet with example of desired output – player0 Aug 19 '20 at 14:48
  • Does this answer your question? [How do you create a "reverse pivot" in Google Sheets?](https://stackoverflow.com/questions/24954722/how-do-you-create-a-reverse-pivot-in-google-sheets) – Rubén Aug 21 '20 at 00:33

3 Answers3

4

Or, as an alternative

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

enter image description here

or

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

enter image description here

JPV
  • 26,499
  • 4
  • 33
  • 48
1

Try this:

=ARRAYFORMULA(SPLIT(Flatten(ARRAYFORMULA(concat(CONCAT(INDEX( FILTER( B1:1 , NOT( ISBLANK( B1:1 ) ) ))," "),INDEX( FILTER( A2:A , NOT( ISBLANK( A2:A ) ) )))))," "))

sample1

or if your format is: A. B. C. :

=ARRAYFORMULA(SPLIT(Flatten(ARRAYFORMULA(concat(SUBSTITUTE(INDEX( FILTER( B1:1 , NOT( ISBLANK( B1:1 ) ) )),"."," "),INDEX( FILTER( A2:A , NOT( ISBLANK( A2:A ) ) )))))," "))

sample2

or if you want it in the same column:

=Flatten(ARRAYFORMULA(concat(INDEX( FILTER( B1:1 , NOT( ISBLANK( B1:1 ) ) )),INDEX( FILTER( A2:A , NOT( ISBLANK( A2:A ) ) )))))

sample3

Marios
  • 26,333
  • 8
  • 32
  • 52
1

Another alternative:

  1. Use an intermediate sheet for storing the following for each column:

enter image description here

  1. Then split the last column into rows:

enter image description here

Let's try
  • 1,044
  • 9
  • 20