another transpose problem

[SeanDamnit]

Hello Collective Conciousnes of Mr. Excel.

I have data that looks like this:

Excel Workbook
	A	B	C	D	E	F
1	Product SKU	RMA?	Description	Quantity	Vendor SKU	Bar Code
39	ACCAHT000354	No	Hard Shell Case Gray EVO	1	376580	888063765803
40	ACCAHT000651	No	HTC EVO SHIFT HARD SHELL BLUE/BLACK	5		70H00370-04M, 821793011307
41	ACCAHT000652	No	HTC EVO SHIFT HARD SHELL PINK/WHITE	5	359583	70H00370-06M, 821793011321, 888063595837
42	ACCALG000269	No	BODY GLOVE LG510	8	375231	043859614332, 888063752315, 9143301, BGCOVRUMTOUCH
43	ACCAMO000366	No	Carry Holster, i580	2	393278	781420011561, 888063932786
44	ACCANL000316	No	NLU 15 Pack of Fitted Screen Protectors	1	304702	878966000644, 888063047022
45	ACCAOT000368	No	Defender Case, 8300	1	352808	660543002314, 888063528088
Sheet1

Note the highlighted Bar Code column

I would like for it to look like this:

Excel Workbook
	A	B	C	D	E	F
1	Product SKU	RMA?	Description	Quantity	Vendor SKU	Bar Code
2	ACCAHT000354	No	Hard Shell Case Gray EVO	1	376580	888063765803
3	ACCAHT000651	No	HTC *EVO SHIFT HARD SHELL BLUE/BLACK	5	*	70H00370-04M
4	ACCAHT000651	No	HTC *EVO SHIFT HARD SHELL BLUE/BLACK	5	*	821793011307
5	ACCAHT000652	No	HTC EVO SHIFT HARD SHELL PINK/WHITE	5	359583	70H00370-06M
6	ACCAHT000652	No	HTC EVO SHIFT HARD SHELL PINK/WHITE	5	359583	821793011321
7	ACCAHT000652	No	HTC EVO SHIFT HARD SHELL PINK/WHITE	5	359583	888063595837
8	ACCALG000269	No	BODY GLOVE LG510	8	375231	43859614332
9	ACCALG000269	No	BODY GLOVE LG510	8	375231	888063752315
10	ACCALG000269	No	BODY GLOVE LG510	8	375231	9143301
11	ACCALG000269	No	BODY GLOVE LG510	8	375231	BGCOVRUMTOUCH
12	ACCAMO000366	No	Carry Holster, i580	2	393278	781420011561
13	ACCAMO000366	No	Carry Holster, i580	2	393278	888063932786
14	ACCANL000316	No	NLU 15 Pack of Fitted Screen Protectors	1	304702	878966000644
15	ACCANL000316	No	NLU 15 Pack of Fitted Screen Protectors	1	304702	888063047022
16	ACCAOT000368	No	Defender Case, 8300	1	352808	660543002314
17	ACCAOT000368	No	Defender Case, 8300	1	352808	888063528088
Sheet2

In other words, for every Bar Code separated by a comma, I'd like to create a new row with duplicate information. I have much more data than the sample set here, and the bar code column can have any number of unique entries, so any solution there is will have to account for that.

I'm doing my best to learn VBA - and seeing how the pros handle this will definitely boost my knowledge.

I appreciate it!

 

[MickG]

Try this :-
Results start "G1".

[COLOR="Navy"]Sub[/COLOR] MG15Sep50
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Bcode [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    ReDim ray(1 To Rng.Count * 4, 1 To 6)
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        Bcode = Split(Dn(, 6), ",")
        [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Bcode)
            c = c + 1
            [COLOR="Navy"]For[/COLOR] Ac = 1 To 5
                ray(c, Ac) = Dn(, Ac)
            [COLOR="Navy"]Next[/COLOR] Ac
                ray(c, 6) = Format(Bcode(n), "0")
        [COLOR="Navy"]Next[/COLOR] n
    [COLOR="Navy"]Next[/COLOR] Dn
Columns("L:L").NumberFormat = "0"
Range("G1").Resize(c, 6) = ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[SeanDamnit]

Try this :-
Results start "G1".

[COLOR="Navy"]Sub[/COLOR] MG15Sep50
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Bcode [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    ReDim ray(1 To Rng.Count * 4, 1 To 6)
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        Bcode = Split(Dn(, 6), ",")
        [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Bcode)
            c = c + 1
            [COLOR="Navy"]For[/COLOR] Ac = 1 To 5
                ray(c, Ac) = Dn(, Ac)
            [COLOR="Navy"]Next[/COLOR] Ac
                ray(c, 6) = Format(Bcode(n), "0")
        [COLOR="Navy"]Next[/COLOR] n
    [COLOR="Navy"]Next[/COLOR] Dn
Columns("L:L").NumberFormat = "0"
Range("G1").Resize(c, 6) = ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

Works perfect, MickG. I appreciate the help - you have no idea how much time this saves me.