Removing duplicates and consolidating different fields

[tonyg88]

Hello Everyone,

I'm really having trouble solving this - it's driving me crazy.

I have a set of data with some duplicate ID's in column A. If the ID is the same, I would like to concatenate the values in the columns to the right with the rows below, but only if they're different. I found a couple of related questions on this site, but can't seem to relate it directly back to my issue here. I tried nested IF statements, but can only figure out how to compare 2 rows, if there are more than 2 duplicate IDs - my formula would not identify them and consolidate into 1 row.

I'd be extremely grateful for anyone who can help me out. See below for an example (not the actual data I'm working with):

ID	Name	Order
1	Jack	Chicken
1	Jill	Pork
2	Jack	Chicken
3	Jack	Rice
4	Jill	Chicken
4	Jack	Chicken
4	Jack	Pork
4	Jill	Rice

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

Here's what I would like the final result to be:

ID	Name	Order
1	Jack, Jill	Chicken, Pork
2	Jack	Chicken
3	Jack	Rice
4	Jill, Jack	Chicken, Pork, Rice

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

 

[MickG]

If your happy to use Code, try this:-
Results start "D1"

[COLOR="Navy"]Sub[/COLOR] MG19Jan01
[COLOR="Navy"]Dim[/COLOR] dic [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] K, p, g, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ps [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Gs [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not dic.Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] dic(Dn.Value) = CreateObject("Scripting.Dictionary")
        [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]If[/COLOR] Not dic(Dn.Value).Exists(Dn.Offset(, 1).Value) [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] dic(Dn.Value)(Dn.Offset(, 1).Value) = CreateObject("Scripting.Dictionary")
                    dic(Dn.Value)(Dn.Offset(, 1).Value).CompareMode = 1
            [COLOR="Navy"]End[/COLOR] If
                dic(Dn.Value)(Dn.Offset(, 1).Value)(Dn.Offset(, 2).Value) = Empty
                      
        [COLOR="Navy"]Next[/COLOR] Dn


[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] dic.keys
    c = c + 1
    Cells(c, "D") = K
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] p [COLOR="Navy"]In[/COLOR] dic(K).keys
            Ps = Ps & ", " & p
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] g [COLOR="Navy"]In[/COLOR] dic(K)(p).keys
                [COLOR="Navy"]If[/COLOR] Not InStr(Gs, g) > 0 [COLOR="Navy"]Then[/COLOR] Gs = Gs & ", " & g
            [COLOR="Navy"]Next[/COLOR] g
        [COLOR="Navy"]Next[/COLOR] p
        Cells(c, "E") = Mid(Ps, 2): Ps = ""
        Cells(c, "F") = Mid(Gs, 2): Gs = ""
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Mike Szczesny]

ID	Name	Order		ID	Name	Name1	Order1	Order2	Order3
1	Jack	chicken		1	Jack	Jill	chicken	pork
1	Jill	pork		2	Jack		chicken
2	Jack	chicken		3	Jack		rice
3	Jack	rice		4	Jack	Jill	chicken	pork	rice
4	Jill	chicken
4	Jack	chicken
4	Jack	pork
4	Jill	rice

<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>
</tbody>

Hi tonyg88,
Perhaps you can use this. It is not perfect for your purposes. Assume data source range is A1:C9. Assume result table headers are in E1:J1. You need to place formulas in E2,F2, and H2. Enter formulas with C-S-E. Copy across, then down
E2:

=IFERROR(INDEX($A$2:$A$9,SMALL(IF(FREQUENCY(IF($A$2:$A$9<>"",MATCH($A$2:$A$9,$A$2:$A$9,0)),ROW($A$2:$A$9)-ROW($A$2)+1),ROW($A$2:$A$9)-ROW($A$2)+1),ROWS($E$2:E2))),"") F2: =IFERROR(INDEX($B$2:$B$9,SMALL(IF(FREQUENCY(IF($A$2:$A$9=$E2,MATCH($B$2:$B$9,$B$2:$B$9,0)),ROW($B$2:$B$9)-ROW($B$2)+1),ROW($B$2:$B$9)-ROW($B$2)+1),COLUMNS($F$2:F2))),"") H2: =IFERROR(INDEX($C$2:$C$9,SMALL(IF(FREQUENCY(IF($A$2:$A$9=$E2,MATCH($C$2:$C$9,$C$2:$C$9,0)),ROW($C$2:$C$9)-ROW($C$2)+1),ROW($C$2:$C$9)-ROW($C$2)+1),COLUMNS($H$2:H2))),"") <tbody> </tbody> <tbody> </tbody>

<tbody>
</tbody>

Hope this helps,
Mike S.