Duplicating Multiple Cells & Columns

[t5timesb]

I am struggling with something and maybe it is the shear number of times I have to do this. So I have output I received from 30,888 cells (lets call them "finished goods" - below my finished good is: "FW15RH20.KBSTVS.GDRB60"). Now, I need to pair each of the "finished goods with 20 different items like shown below. Each "finished goods" cell is paired with 2 other columns (0,A).

This is an example of 1 "finished goods" paired with the columns, remember, I have 30,888 of these, HOW DO I DO THIS?

FW15RH20.KBSTVS.GDRB60 <colgroup><col><col></colgroup><tbody> </tbody> <colgroup><col><col></colgroup><tbody> </tbody>	0	A
FW15RH20.KBSTVS.GDRB60 <tbody> </tbody>	1	B
FW15RH20.KBSTVS.GDRB60 <tbody> </tbody>	2	C
FW15RH20.KBSTVS.GDRB60 <tbody> </tbody>	3	D
FW15RH20.KBSTVS.GDRB60 <tbody> </tbody>	4	E
FW15RH20.KBSTVS.GDRB60 <tbody> </tbody>	5	F
FW15RH20.KBSTVS.GDRB60 <tbody> </tbody>	6	G
FW15RH20.KBSTVS.GDRB60 <tbody> </tbody>	7	H
FW15RH20.KBSTVS.GDRB60 <tbody> </tbody>	8	I
FW15RH20.KBSTVS.GDRB60 <tbody> </tbody>	9	J
FW15RH20.KBSTVS.GDRB60 <tbody> </tbody>	10	K
FW15RH20.KBSTVS.GDRB60 <tbody> </tbody>	11	L
FW15RH20.KBSTVS.GDRB60 <tbody> </tbody>	12	M
FW15RH20.KBSTVS.GDRB60 <tbody> </tbody>	13	N
FW15RH20.KBSTVS.GDRB60 <tbody> </tbody>	14	O
FW15RH20.KBSTVS.GDRB60 <tbody> </tbody>	15	P
FW15RH20.KBSTVS.GDRB60 <tbody> </tbody>	16	Q
FW15RH20.KBSTVS.GDRB60 <tbody> </tbody>	17	R
FW15RH20.KBSTVS.GDRB60 <tbody> </tbody>	18	S
FW15RH20.KBSTVS.GDRB60 <tbody> </tbody>	19	T
FW15RH20.KBSTVS.GDRB60 <tbody> </tbody>	20	U

<tbody>
</tbody>

 

[t5timesb]

Does this make sense to anyone??? I need help!

 

[Gilliam]

so you have 30,888 finished goods imported from elsewhere...
You then need 20 (or 21 (including the 0,A)?) rows for each of the finished goods? so in total 30,888*20 = 617760 Rows of data?
beside each finished good in column B you want the numbers 1-20 (0-20) in order, and in column C the letters A through U (B-U)? for all 617760 rows.

To a third party... no that didnt make much sense.
also, why the red letters?

 

[t5timesb]

Ok. Let me try again. Right now, I have 30,888 rows in Column A. I have 20 rows in Column B. I have 20 rows in Column C.

I need Column A to duplicate itself for 20 times while Column B & C will stay the same. It seems like a simple process, but I need to do it 30,888 times.

 

[t5timesb]

Ok. Let me try again. Right now, I have 30,888 rows in Column A. I have 20 rows in Column B. I have 20 rows in Column C.

I need Column A to duplicate itself for 20 times while Column B & C will stay the same. It seems like a simple process, but I need to do it 30,888 times.

I am happy to pay someone in golf clubs if that helps!

 

[Gilliam]

So column A has unique finished goods, all 30,888 of them?
You then want something like this?

<tbody> </tbody> Finished Good1	0	A
Finished Good1	1	B
...	...	...
Finished Good1	20	U
Finished Good2	0	A
Finished Good2	1	B
...	...	...
Finished Good2	20	U

<tbody>
</tbody>

etc, so in total you have 617760 rows of Data?

 

[t5timesb]

That is 100% correct!

 

[t5timesb]

Does anyone know how to do this?

 

[Gilliam]

Workin on it, few more minutes.

 

[Gilliam]

Does anyone know how to do this?

Sub FinishedGoods()    'with the sheet of imported data active... do the following Macro.
    Application.ScreenUpdating = False
    lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    FGRows = lastRow * 21 'if the numbers go form 0-20 that's 21 numbers...
    ActiveWorkbook.Worksheets.Add After:=Worksheets(Worksheets.Count) 'I'll just put everything in a new worksheet
    ActiveSheet.Name = "Finished Goods" ' named Finished Goods
    ActiveCell.FormulaR1C1 = "=INDIRECT(""Sheet1!A""&INT(ROW(R[20]C)/21))" 'this will be VERY SLOW
    Range("B1").FormulaR1C1 = "=MOD(ROW()+20,21)" 'this gets the numbers 0-20 in column B
    Range("C1").FormulaR1C1 = "=CHAR(RC[-1]+65)" 'this gets the letters A thru U in column C
    Range("A1:C1").AutoFill Destination:=Range("A1:C" & FGRows) 'because it's slow, I will paste over itself with just the values.
    Range("A1:C" & FGRows).Copy
    Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Columns("A:A").EntireColumn.AutoFit
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Yea, it took about 10-12 seconds to run the macro, but it works!