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

Product		Store A	Store B	Store C
KOMBUCHA VANILLA		3.99 (2.99 SAL)	3.99 (2.99 SAL)	3.99 (2.99 SAL)
KOMBUCHA RED		3.99 (2.99 SAL)	3.99 (2.99 SAL)	3.99 (2.99 SAL)
YOGURT DRINK		5.99 (4.79 SAL)
YOGURT DRINK			5.99 (4.79 SAL)	5.99 (4.79 SAL)
YOGURT BANANA 4PK		5.99 (4.79 SAL)
YOGURT BANANA 4PK			5.99 (4.79 SAL)	5.99 (4.79 SAL)
YOGURT BERRY 4PK		5.99 (4.79 SAL)
COFFEE		3.99 (3.39 SAL)	3.99 (3.39 SAL)	3.99 (3.39 SAL)

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

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

Product		Store A	Store B	Store C
KOMBUCHA VANILLA		3.99 (2.99 SAL)	3.99 (2.99 SAL)	3.99 (2.99 SAL)
KOMBUCHA RED		3.99 (2.99 SAL)	3.99 (2.99 SAL)	3.99 (2.99 SAL)
YOGURT DRINK		5.99 (4.79 SAL)	5.99 (4.79 SAL)	5.99 (4.79 SAL)
YOGURT BANANA 4PK		5.99 (4.79 SAL)	5.99 (4.79 SAL)	5.99 (4.79 SAL)
COFFEE		3.99 (3.39 SAL)	3.99 (3.39 SAL)	3.99 (3.39 SAL)

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

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]

What happened to YOGURT BERRY 4PK?

<tbody>
</tbody>

 

[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