How to combine rows with duplicate info into one based on a column

[texhorn92]

I'm looking for a more efficient way of combining possible duplicate rows depending on the column "Product." Some rows will not have duplicates. Here is a sample of the data I'm working with. In reality, I am working with thousands of these rows and over 40 columns


[TABLE="width: 824"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Product[/TD]
[TD][/TD]
[TD]Store A[/TD]
[TD]Store B[/TD]
[TD]Store C[/TD]
[/TR]
[TR]
[TD]KOMBUCHA VANILLA[/TD]
[TD][/TD]
[TD]3.99 (2.99 SAL)[/TD]
[TD]3.99 (2.99 SAL)[/TD]
[TD]3.99 (2.99 SAL)[/TD]
[/TR]
[TR]
[TD]KOMBUCHA RED[/TD]
[TD][/TD]
[TD]3.99 (2.99 SAL)[/TD]
[TD]3.99 (2.99 SAL)[/TD]
[TD]3.99 (2.99 SAL)[/TD]
[/TR]
[TR]
[TD]YOGURT DRINK[/TD]
[TD][/TD]
[TD]5.99 (4.79 SAL)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YOGURT DRINK[/TD]
[TD][/TD]
[TD][/TD]
[TD]5.99 (4.79 SAL)[/TD]
[TD]5.99 (4.79 SAL)[/TD]
[/TR]
[TR]
[TD]YOGURT BANANA 4PK[/TD]
[TD][/TD]
[TD]5.99 (4.79 SAL)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]YOGURT BANANA 4PK[/TD]
[TD][/TD]
[TD][/TD]
[TD]5.99 (4.79 SAL)[/TD]
[TD]5.99 (4.79 SAL)[/TD]
[/TR]
[TR]
[TD]YOGURT BERRY 4PK[/TD]
[TD][/TD]
[TD]5.99 (4.79 SAL)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]COFFEE[/TD]
[TD][/TD]
[TD]3.99 (3.39 SAL)[/TD]
[TD]3.99 (3.39 SAL)[/TD]
[TD]3.99 (3.39 SAL)[/TD]
[/TR]
</tbody>[/TABLE]


After combining the duplicate rows (using Product), I would achieve something like this:


[TABLE="width: 824"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Product[/TD]
[TD][/TD]
[TD]Store A[/TD]
[TD]Store B[/TD]
[TD]Store C[/TD]
[/TR]
[TR]
[TD]KOMBUCHA VANILLA[/TD]
[TD][/TD]
[TD]3.99 (2.99 SAL)[/TD]
[TD]3.99 (2.99 SAL)[/TD]
[TD]3.99 (2.99 SAL)[/TD]
[/TR]
[TR]
[TD]KOMBUCHA RED[/TD]
[TD][/TD]
[TD]3.99 (2.99 SAL)[/TD]
[TD]3.99 (2.99 SAL)[/TD]
[TD]3.99 (2.99 SAL)[/TD]
[/TR]
[TR]
[TD]YOGURT DRINK[/TD]
[TD][/TD]
[TD]5.99 (4.79 SAL)[/TD]
[TD]5.99 (4.79 SAL)[/TD]
[TD]5.99 (4.79 SAL)[/TD]
[/TR]
[TR]
[TD]YOGURT BANANA 4PK[/TD]
[TD][/TD]
[TD]5.99 (4.79 SAL)[/TD]
[TD]5.99 (4.79 SAL)[/TD]
[TD]5.99 (4.79 SAL)[/TD]
[/TR]
[TR]
[TD]COFFEE[/TD]
[TD][/TD]
[TD]3.99 (3.39 SAL)[/TD]
[TD]3.99 (3.39 SAL)[/TD]
[TD]3.99 (3.39 SAL)[/TD]
[/TR]
</tbody>[/TABLE]


Any ideas on how I could make this process more efficient. I am currently doing this manually and it is very painful. Thanks!

 

[texhorn92]

Any ideas?

 

[Leo Skywalker]

[TABLE="class: cms_table, width: 824"]
<tbody>[TR]
[TD]What happened to YOGURT BERRY 4PK?

[/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

Try this for results on sheet 2.

[COLOR="Navy"]Sub[/COLOR] MG15Jul45
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, Dic [COLOR="Navy"]As[/COLOR] Object, rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = ActiveSheet.UsedRange
ReDim nRay(1 To UBound(Ray, 1), 1 To UBound(Ray, 2))
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
     [COLOR="Navy"]For[/COLOR] n = 1 To UBound(Ray, 1)
           [COLOR="Navy"]If[/COLOR] Not Dic.Exists(Ray(n, 1)) [COLOR="Navy"]Then[/COLOR]
                rw = rw + 1
                [COLOR="Navy"]For[/COLOR] Ac = 1 To UBound(Ray, 2)
                    nRay(rw, Ac) = Ray(n, Ac)
                [COLOR="Navy"]Next[/COLOR] Ac
                Dic.Add Ray(n, 1), rw
            [COLOR="Navy"]Else[/COLOR]
                [COLOR="Navy"]For[/COLOR] Ac = 1 To UBound(Ray, 2)
                    [COLOR="Navy"]If[/COLOR] IsEmpty(nRay(Dic(Ray(n, 1)), Ac)) [COLOR="Navy"]Then[/COLOR]
                       nRay(Dic(Ray(n, 1)), Ac) = Ray(n, Ac)
                    [COLOR="Navy"]End[/COLOR] If
                [COLOR="Navy"]Next[/COLOR] Ac
            [COLOR="Navy"]End[/COLOR] If
     [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(Dic.Count, UBound(Ray, 2))
    .Value = nRay
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick