Previous Topic Next Topic

How to repeat a row 4 times, while increasing sequence

Solved/Closed
Stumped.. - Dec 10, 2014 at 03:53 AM
OldCoder Posts 1 Registration date Thursday February 5, 2015 Status Member Last seen February 6, 2015 - Feb 6, 2015 at 12:30 AM
Hello all,

I need help on the best way to repeat this 4 times, and then increase the number at the end. I need this:

Column A
AAA_BBB_0002135

To become:

Column A
AAA_BBB_0002135
AAA_BBB_0002135
AAA_BBB_0002135
AAA_BBB_0002135
AAA_BBB_0002136
AAA_BBB_0002136
AAA_BBB_0002136
AAA_BBB_0002136
AAA_BBB_0002137
AAA_BBB_0002137
AAA_BBB_0002137
AAA_BBB_0002137

Currently, I am copying and pasting cell A2 into A3-A5. Then using the auto-fill drag down to increase the number by one, and then copying and pasting again. I am hoping there is a simple macro to run the entire thing for me. I need it to repeat the pattern at least 700 times.

Any help is greatly appreciated.
Related:

1 reply

Answer 1 / 1
OldCoder
Feb 5, 2015 at 12:02 AM
Try
=CONCATENATE("AAA_BBB_",REPT("0",3),2134+INT((ROW()-1)/4)+1)
if you start your data in any row except 1 change the -1 to match the row the data starts in.
0
OldCoder Posts 1 Registration date Thursday February 5, 2015 Status Member Last seen February 6, 2015
Feb 6, 2015 at 12:30 AM
Had a thought after i posted that he might actually need all the digits he listed in his example this expands the number option significantly. Change options in the formula;
-- Use the row number in place of the -1.
-- Replace the 2135 with the number you want to start with.
-- Replace the /4 with the number of times you want to repeat.
-- Replace the "AAA_BBB_" with any other text you'd like to use.

=CONCATENATE("AAA_BBB_",TEXT(2135+INT((ROW()-1)/4),"0000000"))
0