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):

[TABLE="width: 424"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]ID
[/TD]
[TD]Name[/TD]
[TD]Order[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Jack[/TD]
[TD]Chicken
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Jill
[/TD]
[TD]Pork[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jack[/TD]
[TD]Chicken[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jack[/TD]
[TD]Rice[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jill[/TD]
[TD]Chicken[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jack[/TD]
[TD]Chicken[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jack[/TD]
[TD]Pork[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jill[/TD]
[TD]Rice
[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 424"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]ID
[/TD]
[TD]Name[/TD]
[TD]Order[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Jack, Jill[/TD]
[TD]Chicken, Pork[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Jack[/TD]
[TD]Chicken[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jack[/TD]
[TD]Rice[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Jill, Jack[/TD]
[TD]Chicken, Pork, Rice[/TD]
[/TR]
</tbody>[/TABLE]

 

[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]

[TABLE="width: 640"]
<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]ID[/TD]
[TD="width: 64"]Name[/TD]
[TD="width: 64"]Order[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]ID[/TD]
[TD="width: 64"]Name[/TD]
[TD="width: 64"]Name1[/TD]
[TD="width: 64"]Order1[/TD]
[TD="width: 64"]Order2[/TD]
[TD="width: 64"]Order3[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Jack[/TD]
[TD]chicken[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD]Jack[/TD]
[TD]Jill[/TD]
[TD]chicken[/TD]
[TD]pork[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Jill[/TD]
[TD]pork[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]Jack[/TD]
[TD][/TD]
[TD]chicken[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Jack[/TD]
[TD]chicken[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]Jack[/TD]
[TD][/TD]
[TD]rice[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Jack[/TD]
[TD]rice[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD]Jack[/TD]
[TD]Jill[/TD]
[TD]chicken[/TD]
[TD]pork[/TD]
[TD]rice[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Jill[/TD]
[TD]chicken[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Jack[/TD]
[TD]chicken[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Jack[/TD]
[TD]pork[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Jill[/TD]
[TD]rice[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]




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: [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =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: [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =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: [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =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))),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hope this helps,
Mike S.