I have these two columns in Google sheet:
| Region | Traffic |
|---|---|
| Japan | Online |
| Korea | Offline |
| Ghana | Mix |
I would like a way to use an automatic formula that creates this:
| Region | Traffic |
|---|---|
| Japan | Online |
| Japan | Offline |
| Japan | Mix |
| Korea | Online |
| Korea | Offline |
| Korea | Mix |
| Ghana | Online |
| Ghana | Offline |
| Ghana | Mix |
Any idea on how to do this in google sheets? :)
CodePudding user response：
try:
=INDEX(TRIM(SPLIT(FLATTEN(SPLIT(REPT(QUERY(
FILTER({A2:A&"♠"&B2:B&"♣"}, A2:A<>""),,9^9), D1), "♣")), "♠")))
or:
=INDEX(SPLIT(FLATTEN(FILTER(A2:A, A2:A<>"")&"♦"&TRANSPOSE(FILTER(B2:B, B2:B<>""))), "♦"))
