Best way to combine semi-duplicate rows into one column?

[sabot7726]

Greetings everyone!

I'm looking for a way to combine several duplicate rows of data into one by combining columns. Here's a sample, as you can see column A and B don't change but columns C and D could be different combinations. I'm looking to see if I can combine the repeating rows into one by adding the different data to the C column and removing the D column by adding it into C somehow.

For example Jim Smith would go from 4 rows to 1 that looked similar to: TR10000144 | Jim Smith | ICS 700 (Pre-deployment) (Pending Completion Approval)/ICS-700 (Completed)/ICS-100 (Pre deployment)(Completed)/ICS-100(Completed)|

Excel 2012
	A	B	C	D
1	TR Identification Number	User Full Name	Training Title	Transcript Status
2	TR10000144	Jim Smith	ICS 700 (Pre-deployment Basic Course)	Pending Completion Approval
3	TR10000144	Jim Smith	ICS-700	Completed
4	TR10000144	Jim Smith	ICS 100 (Pre-deployment Basic Course)	Completed
5	TR10000144	Jim Smith	ICS-100	Completed
6	TR0008365	David Copperfield	ICS 700 (Pre-deployment Basic Course)	Completed
7	TR0008365	David Copperfield	ICS 100	Completed
8	TR0008365	David Copperfield	ICS 100 (Pre-deployment Basic Course)	Completed
9	TR0008365	David Copperfield	ICS 700	Completed
10	TR0008365	David Copperfield	IS-700a	Completed
11	TR0008365	David Copperfield	IS-100b	Completed
12	TR0013751	Wonder, Woman	FEMA ICS-100	Completed
13	TR0013751	Wonder, Woman	ICS-700	Completed
14	TR10001115	Super, Man	ICS 700 (Pre-deployment Basic Course)	Pending Approval
15	TR10001115	Super, Man	IS-100	Completed
16	TR10001115	Super, Man	IS-700	Completed
Sheet1

I really appreciate it, I've tried a few things but it throws me that sometimes there's 2 duplicates and sometimes there's up to 7, not sure how to work around that. Thanks!

-Mike

 

[MickG]

Try this, Results Start "F1"

[COLOR="Navy"]Sub[/COLOR] MG10Dec12
[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]
[COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
ReDim Ray(1 To Rng.Count, 1 To 3)
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
    [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]
            n = n + 1
            Ray(n, 1) = Dn.Value: Ray(n, 2) = Dn.Offset(, 1).Value
            Ray(n, 3) = Dn.Offset(, 2).Value & "," & Dn.Offset(, 3).Value
            Dic.Add Dn.Value, n
        [COLOR="Navy"]Else[/COLOR]
            Ray(Dic(Dn.Value), 3) = Ray(Dic(Dn.Value), 3) & "," & Dn.Offset(, 2).Value & "," & Dn.Offset(, 3).Value
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]With[/COLOR] Range("F1").Resize(Dic.Count, 3)
.Value = Ray
.Columns.AutoFit
.Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[sabot7726]

Thanks so much! This works great I really appreciate it

 

[MickG]

You're welcome