1

I stumped on how to copy a column with unique cell contents into another column but with repeated N entries of each row with Google Sheets.

In sheet 1:

A1:A

A2:AA

A3:AAC

A4:AACQ

A5:AAIC

A6:AAL

A7:AAN

A8:AAON

A9:AAP

A10:AAPL

A11:AAU

A12:AAWW

A13:ABB

This is what I did in sheet 2:

A1:=sheet1!A1 result is A (desired result)

A2:=sheet1!A1 result is A (desired result)

A3:=sheet1!A1 result is A (desired result)

A4:=sheet1!A2 result is AA (desired result)

A5:=sheet1!A2 result is AA (desired result)

A6:=sheet1!A2 result is AA (desired result)

I then highlight cells A1:A6 and drag down but my result is:

A1:A

A2:A

A3:A

A4:AA

A5:AA

A6:AA

A7:AAN =sheet1!A8 (want this to be =sheet1!A3)

A8:AAN =sheet1!A8 (want this to be =sheet1!A3)

A9:AAN =sheet1!A8 (want this to be =sheet1!A3)

A10:AAON =sheet1!A9 (want this to be =sheet1!A4)

A11:AAON =sheet1!A9 (want this to be =sheet1!A4)

A12:AAON =sheet1!A9 (want this to be =sheet1!A4)

A13:ABB =sheet1A14 (want this to be =sheet1!A5)

A14:ABB =sheet1!A14 (want this to be =sheet1!A5)

A15:ABB =sheet1!A14 (want this to be =sheet1!A5)

Is there a way to get my desired result without scripting?

1 Answer 1

1

Rather than dragging, one formula can handle this:

=ArrayFormula(FLATTEN(SPLIT(FILTER(REPT(Sheet1!A:A&"|",3),Sheet1!A:A<>""),"|",1)))

This will form a virtual array of a triplicate of everything in Sheet1!A:A, each separated by the pipe symbol (|), only for occupied rows. Then SPLIT will separate them at that pipe symbol, and FLATTEN will make one column of it all.

2
  • Hi Erik, your suggested formula worked perfectly. Thank you for providing guidance and introducing me to SPLIT and FLATTEN! Much appreciated. Have a great day!
    – James
    Jun 15, 2021 at 18:48
  • You're welcome, James. Always happy to pass along long-term knowledge with immediate help.
    – Erik Tyler
    Jun 15, 2021 at 19:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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