Duplication with numerical increments

[Llewellyn1972]

Hi,

I have the following in column A.

2G_10A
2G_10B
2G_10C
2G_10D
3G_10A1
3G_10A2
3G_10A3
3G_10A4
3G_10B1
3G_10B2
3G_10B3
3G_10B4
3G_10C1
3G_10C2
3G_10C3
3G_10C4
3G_10D1
3G_10D2
3G_10D3
3G_10D4
4G_10A1
4G_10A2
4G_10A3
4G_10B1
4G_10B2
4G_10B3
4G_10C1
4G_10C2
4G_10C3
4G_10D1
4G_10D2
4G_10D3

I need to have this block of cells duplicated 2000 times , but with every duplication the value for this example being "10" and only "10" , needs to increment by 1.

Below is what I should see for the first two blocks

2G_10A
2G_10B
2G_10C
2G_10D
3G_10A1
3G_10A2
3G_10A3
3G_10A4
3G_10B1
3G_10B2
3G_10B3
3G_10B4
3G_10C1
3G_10C2
3G_10C3
3G_10C4
3G_10D1
3G_10D2
3G_10D3
3G_10D4
4G_10A1
4G_10A2
4G_10A3
4G_10B1
4G_10B2
4G_10B3
4G_10C1
4G_10C2
4G_10C3
4G_10D1
4G_10D2
4G_10D3
2G_11A
2G_11B
2G_11C
2G_11D
3G_11A1
3G_11A2
3G_11A3
3G_11A4
3G_11B1
3G_11B2
3G_11B3
3G_11B4
3G_11C1
3G_11C2
3G_11C3
3G_11C4
3G_11D1
3G_11D2
3G_11D3
3G_11D4
4G_11A1
4G_11A2
4G_11A3
4G_11B1
4G_11B2
4G_11B3
4G_11C1
4G_11C2
4G_11C3
4G_11D1
4G_11D2
4G_11D3

now just have the above duplicated 1998 more times.
Thanks

[Glenn Kennedy]

Maybe this... see sheet.

Please Login or Register  to view this content.

It's in the gold cells. You can delete columns B, C & D if this is correct. I have left them in, just in case I have missed the point of your query.

[Llewellyn1972]

Hi Glen,

Thank you very much for your assistance.
I have noted that when I use the bottom right corner method (+) and pulling down the cells towards the 2000 number range at row 2882 it omits the last characters, as per the attachment.Looks like the formula omits the last characters after 99.

Any reason why this would happen?

Thanks
Llewellyn

Book 1.jpg

[Glenn Kennedy]

This will work with your dataset... and is simpler...

=LEFT(INDEX($A$2:$A$33,MOD((ROWS(A$2:A2)-1),32)+1),3)&10+INT((ROWS($A$2:A2)-1)/32)&MID(INDEX($A$2:$A$33,MOD((ROWS(A$2:A2)-1),32)+1),6,255)

[Llewellyn1972]

Hi Glen,

Thank you very much. The formula work very well.
Thanks

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.