Is there a VBA solution for this?

[itgirlxx]

Source table:

Frame	Fabric	Size
ATB	G901	C
ATBN	G902	K
FMB	G903	Q
FMBN	G904	F
	G905	T

<tbody>
</tbody>

I would like to create 1 row in a new table for every combination of these 3 columns with a '-' separator between each value.
For example, for ATB, I need 1 row for each fabric in each of the 5 sizes, so 25 SKU's:
ATB-G901-C
ATB-G901-K
and so on until ATB-G905-T

This is just a very small snapshot of the true table.
In the true table there are close to 130 frames and 150 fabrics.

Many thanks in advance for any help given.

 

[mrmmickle1]

Maybe try something like this.....

Data is on Sheet 1
Output goes on Sheet 2

[COLOR=#0000ff]Sub[/COLOR] Test()

    [COLOR=#0000ff]Dim[/COLOR] Col1LR  [COLOR=#0000ff]As Long[/COLOR]
    [COLOR=#0000ff]Dim[/COLOR] Col2LR  [COLOR=#0000ff]As Long[/COLOR]
 [COLOR=#0000ff]   Dim [/COLOR]Col3LR [COLOR=#0000ff] As Long[/COLOR]
    [COLOR=#0000ff]Dim[/COLOR] DataSht [COLOR=#0000ff]As[/COLOR] Worksheet
   [COLOR=#0000ff] Dim[/COLOR] MidLp   [COLOR=#0000ff]As Long[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] OuterLP[COLOR=#0000ff] As Long[/COLOR]
  [COLOR=#0000ff]  Dim [/COLOR]InnerLp [COLOR=#0000ff]As Long[/COLOR]
  [COLOR=#0000ff]  Dim[/COLOR] nRow    [COLOR=#0000ff]As Long[/COLOR]
    
    [COLOR=#0000ff]Set[/COLOR] DataSht = Sheets("Sheet1")
    
   [COLOR=#0000ff] With[/COLOR] DataSht
    Col1LR = .Range("A" & Rows.Count).End(xlUp).Row
    Col2LR = .Range("B" & Rows.Count).End(xlUp).Row
    Col3LR = .Range("C" & Rows.Count).End(xlUp).Row

     [COLOR=#0000ff]   For [/COLOR]OuterLP = 2 [COLOR=#0000ff]To[/COLOR] Col1LR
           [COLOR=#0000ff] For[/COLOR] MidLp = 2 [COLOR=#0000ff]To [/COLOR]Col2LR
              [COLOR=#0000ff]  For[/COLOR] InnerLp = 2 [COLOR=#0000ff]To[/COLOR] Col3LR
                    nRow = nRow + 1
                    Sheets("Sheet2").Cells(nRow, 1) = .Cells(OuterLP, 1) & "-" _
                                                       & .Cells(MidLp, 2) & "-" _
                                                       & .Cells(InnerLp, 3)
           [COLOR=#0000ff]     Next[/COLOR] InnerLp
         [COLOR=#0000ff]   Next [/COLOR]MidLp
       [COLOR=#0000ff] Next[/COLOR] OuterLP

[COLOR=#0000ff]    End With[/COLOR]

[COLOR=#0000ff]End Sub[/COLOR]

 

[itgirlxx]

I am not proficient with VBA so need very basic instructions on what to do with this code.
This is what I have done so far:
- opened my excel file
- Alt-F11 to open VB
- Insert - Module
- Pasted the code
- Saved the file as a macro-enabled file

Now what?

 

[itgirlxx]

I think I just figured it. I just ran the macro titled Sheet1:test
I assume that it will work in the same way when I add more rows to columns A and B?

I am not proficient with VBA so need very basic instructions on what to do with this code.
This is what I have done so far:
- opened my excel file
- Alt-F11 to open VB
- Insert - Module
- Pasted the code
- Saved the file as a macro-enabled file

Now what?

 

[itgirlxx]

Can you provide me with a 2nd solution if I have just 2 columns of data?
Source table:

Frame	Fabric
ESO	G101
PSO	G102
GL1	G103

<tbody>
</tbody>

I think I just figured it. I just ran the macro titled Sheet1:test
I assume that it will work in the same way when I add more rows to columns A and B?

 

[mrmmickle1]

It's dynamic like you suggest:

I assume that it will work in the same way when I add more rows to columns A and B?

You will need this modification for only two rows of data....

[COLOR=#0000ff]Sub [/COLOR]Test()


[COLOR=#0000ff]    Dim[/COLOR] Col1LR  [COLOR=#0000ff]As Long[/COLOR]
  [COLOR=#0000ff]  Dim [/COLOR]Col2LR  [COLOR=#0000ff]As Long[/COLOR]
[COLOR=#0000ff]    Dim [/COLOR]DataSht As Worksheet
[COLOR=#0000ff]    Dim[/COLOR] MidLp  [COLOR=#0000ff] As Long[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] OuterLP [COLOR=#0000ff]As Long[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] nRow    [COLOR=#0000ff]As Long[/COLOR]
[COLOR=#0000ff]    [/COLOR]
[COLOR=#0000ff]    Set[/COLOR] DataSht = Sheets("Sheet1")[COLOR=#008000] 'Set Data Sheet[/COLOR]
    
[COLOR=#0000ff]    With [/COLOR]DataSht
    Col1LR = .Range("A" & Rows.Count).End(xlUp).Row [COLOR=#008000]'Define last row in Column A[/COLOR]
    Col2LR = .Range("B" & Rows.Count).End(xlUp).Row [COLOR=#008000]'Define last row in Column B[/COLOR]

 [COLOR=#008000]       'Loop through all possible outcomes[/COLOR]
[COLOR=#0000ff]        For [/COLOR]OuterLP = 2 [COLOR=#0000ff]To [/COLOR]Col1LR
 [COLOR=#0000ff]           For [/COLOR]MidLp = 2[COLOR=#0000ff] To[/COLOR] Col2LR
              
                    nRow = nRow + 1
                    Sheets("Sheet2").Cells(nRow, 1) = .Cells(OuterLP, 1) & "-" _
                                                       & .Cells(MidLp, 2)[COLOR=#008000] 'Input Value on Sheet2[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]            Next [/COLOR]MidLp
[COLOR=#0000ff]        Next [/COLOR]OuterLP


[COLOR=#0000ff]    End With[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

Make Sure Sheets Are named exact: "Sheet1" and "Sheet2"
Open VBE Alt + F11
CLick on Insert > Module
Insert Code in Module
Hit F5 Key

Please let me know if you have any questions. Glad to help.