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?

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

 

[t5timesb]

I sent you a message as it wasn't working for me.

 

[t5timesb]

The first didn't work for me. Anyone else help?

 

[Gilliam]

The first didn't work for me. Anyone else help?

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 fromm 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 = "=VLOOKUP(CHAR(RC[-1]+65),{""A"",""CUTOFF"";""B"",""SHAFT1"";""C"",""FERRULE"";""D"",""FERRULEFW15"";""E"",""GRIP"";""F"",""GRIP1"";""G"",""MATCHPOINT"";""H"",""CLUBHEAD"";""I"",""ASSEMBLY"";""J"",""GRINDFERRULES"";""K"",""LOFT/LIE"";""L"",""LASER"";""M"",""CLEAN"";""N"",""BAND"";""O"",""ISLABEL"";""P"",""PACK"";""Q"",""BXSET15"";""R"",""INCFREIGHT"";""S"",""DIRECTLA"";""T"",""VARIABLEOH"";""U"",""FIXEDOH""},2,TRUE)" '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