Trouble with merging/consolidating

[ScottF12]

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.

[#2]

@ScottF12 

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)
            Else
                Set rng = Union(Cells(r, 1), rng)
            End If
        End If
    Next r
    rng.EntireRow.Delete
    Application.ScreenUpdating = True
End Sub

[#3]

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

[#4]

@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?

[#5]

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

https://exceloffthegrid.com/power-query-introduction/ 

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

https://docs.microsoft.com/en-us/powerquery-m/ 

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.

[#6]

@Riny_van_Eekelen 

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

[#7]

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

[#8]

@ScottF12 

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