1

I have a list of rows, and I like to transpose them into a list of columns.

But the trick is how do I repeat each of the new columns 4 times (1 row will become 4 columns)?
Alternatively, how do I add a suffix to each new column (1 row will become 4 columns but each column will have a different suffix)?

To clarify, here are example input and output.

Input:

A
1 a
2 b
3 c

Needed output in C1:

A B C D E F G H I J K L M N
1 a a a a a b b b b c c c c
2 b
3 c

Alternatively:

A B C D E F G H I J K L M N
1 a a suffix1 a suffix2 a suffix3 a suffix1 b suffix1 b suffix2 b suffix3 b suffix4 c suffix1 c suffix2 c suffix3 c suffix4
2 b
3 c
1
  • 1
    I don't understand which further details are needed to reopen this question (which answer was put here in good faith to spare time for others). It presents the input and elaborates which output is needed.
    – LWC
    Commented Apr 14, 2023 at 19:38

1 Answer 1

1

I hope it's okay to answer, I just thought it will help others: C1 should be =arrayformula(SPLIT(JOIN(",",REPT(filter(unique(A:A), unique(A:A)<>"")&",",4)),",")).

Basically it joins the rows using a comma, then splits them horizontally (since that's how Split works, thus sparing us from using Transpose).

  • Filter with <>"" is needed to avoid blanks rows (otherwise you need to limit manually, for example A1:A52 when A53 and beyond aren't used).
  • Unique is needed because it otherwise fails when there are duplicates in column A.

Alternatively, there's a way to get manual values instead of repetition by making C1 into =ARRAYFORMULA(TRANSPOSE(FLATTEN(CONCAT(filter(unique(A:A), unique(A:A)<>""),{concatenate(" ", "suffix1"), concatenate(" ", "suffix2"), concatenate(" ", "suffix3"), concatenate(" ", "suffix4")}))))

Then C1's output will be a suffix 1, d1's will be a suffix 2...and g1 will likewise be b suffix1 and so on.

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