Excel 2007 : merge. deduped problem - Excel

Hi,

I have an unusual excel problem that I can't fix.

In the attached spreadsheet you will see a number of records.

What I need to achieve is to run a macro or formula that will merge the many customer records into 1 consolidated record or row. As part of this process, I also need to remove any duplicated events or other duplicated information.

I also need the seminar cell to incorporate all the unique events that a particular customer has been to, and seperate these by commas.

I have attached a file that shows the original data and the required consolidated data.

Hopefully somebody can help me.

Cheers

Dean

Try this:-
Results sheet(2).

Sub MG30Jul58
Dim Rng     As Range
Dim Dn      As Range
Dim Q       As Variant
Dim Fd      As Boolean
Dim oSpl    As Variant
Dim n       As Integer

Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    With CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare

For Each Dn In Rng
    If Not .Exists(Dn.Value) Then
        .Add Dn.Value, Array(Dn, Dn(, 2), Dn(, 3), Dn(, 4), Dn(, 5), Dn(, 6))
    Else
        Q = .Item(Dn.Value)
            oSpl = Split(Q(5), ",")
                Fd = False
                    For n = 0 To UBound(oSpl)
                        If oSpl(n) = Dn(, 6) Then Fd = True: Exit For
                    Next n
             If Fd = False Then Q(5) = Q(5) & "," & Dn(, 6)
        .Item(Dn.Value) = Q
    End If
Next
Sheets("Sheet2").Range("A1").Resize(.Count, 6) = Application.Index(.items, Evaluate("row(1:" & .Count & ")"), Array(1, 2, 3, 4, 5, 6))
End With

End Sub

Regards Mick

That's very cool, thanks.

So If I were to add new columns to the sheet, would this break the sub routine or is it flexible enough just to recognise the column headers that were set up?
Dean

Hi,

I have added an example spredsheet containing 2 sheets as examples. The merging results are the same, but presumably the sub routine is row specific.

Any help would be great.

Dean