Trouble with merging/consolidating

New Contributor

I would like to merge/delete any duplicates in columns A and B and merge any "X"s in columns C-F all into one row for each person. I've tried so many functions at this point after searching and nothing seems to work for what I am trying to do. Any help would be greatly appreciated. I thought about doing it manually but with 4000+ entries it would just be way to time consuming.excel snap.png

7 Replies


Run this macro:

Sub MergeAndCombine()
    Dim r As Long
    Dim c As Long
    Dim m As Long
    Dim rng As Range
    Application.ScreenUpdating = False
    m = Cells(Rows.Count, 1).End(xlUp).Row
    For r = m To 2 Step -1
        If Cells(r - 1, 1).Value = Cells(r, 1).Value Then
            For c = 3 To 6
                Cells(r - 1, c).Value = Cells(r - 1, c).Value & Cells(r, c).Value
            Next c
            If rng Is Nothing Then
                Set rng = Cells(r, 1)
                Set rng = Union(Cells(r, 1), rng)
            End If
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

@ScottF12 As an alternative to VBA you could consider PowerQuery, as demonstrated in the attached file.




@Riny_van_Eekelen Interesting. I played around with it and wasn't able to reproduce the result. Is there a write up that could explain this process better?

@ScottF12 PowerQuery (PQ) isn't difficult, but it takes some time to get used to and in this particular case I used a few "non-standard" steps. But that doesn't mean one can't learn the tricks. A good starting point would be the link below: 

And then you need to learn about M, the language that drives PQ. More about that in the link below. 


I'm not suggesting it's an easy way, but I dare to say it's easier to learn than VBA. But that's just my personal opinion.


Another variant with Power Query

- select Names and Emails

- unpivot other columns

- select Attribute and Names (in this order)

- pivot table without aggregation with Value as values

@Sergei Baklan Mmmmm..... why didn't I think of that one? Guess I was too eager finding the difficult way to solve the problem.


Once Lambda helper functions are available in 365, it should be possible to solve such problems with regular worksheet formulas.