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

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.

– Rubén
Sep 4 '19 at 16:35
• Possible duplicate of Repeat range under each other in Google Spreadsheets
– Rubén
Sep 4 '19 at 16:49
• Both variants are not good, in script and formula u need to specify range, not column (( Sep 4 '19 at 17:20
– Rubén
Sep 4 '19 at 17:25

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

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

• I'm posting this in case that the possible duplicate be hard to follow as the accepted answer suggest the use of a custom function (script) and the answer that provides a formula doesn't include any explanation about how it works.
– Rubén
Sep 4 '19 at 17:20
• now its working with numbers, but giving only ,,,,, in case of tags like LBA24 Sep 4 '19 at 17:41
• @Mykyta I'm sorry, there was a typo (replace COUNT by COUNTA)
– Rubén
Sep 4 '19 at 17:45
• already, now works perfect thank you a lot Sep 4 '19 at 17:48