How to duplicate range of cells n-times(range can variate and must be based on column)

Question

Can someone explain for me how with the help of formula I can solve this issue? We have an input range of Tags (letters and numbers). Whole massive must be duplicated in the same order n times. The formula should work with column D-D, not with range D1-D6 because number of input values can be changed.

The idea is that the amount of elements in the input column can variate.

Answer 1

Maybe the easier way to get the expected result is by using Copy & Paste

1. Select the range to be copied (D2:D7)
2. Copy
3. Select the destination range (F2:F31)
4. Paste

If your still prefer to use a formula, the following one will return the required values but not the formatting:

=TRANSPOSE(SPLIT(REPT(JOIN(",",D2:D7)&",",E2),","))

To apply the formatting, you could use conditional formatting

Advanced

If you need to use "D:D" instead of D2:D7, replace it by

Array_Constrain(D2:D;1,COUNTA(D2:D))

Use the use of D2 as the start cell instead of D because the column include a header and it should not be included.

Remarks:

COUNTA will work for TEXT values. For NUMBERS use COUNT and if you have mixed value types use LEN(D2:D)>0;

Related

• Repeat range under each other in Google Spreadsheets