Jan 27 2022 05:30 AM
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.
Jan 27 2022 06:01 AM
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
Jan 27 2022 06:16 AM
@ScottF12 As an alternative to VBA you could consider PowerQuery, as demonstrated in the attached file.
Jan 27 2022 11:05 AM
@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?
Jan 27 2022 11:37 AM
@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.
Jan 27 2022 11:59 AM
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
Jan 27 2022 12:03 PM
@Sergei Baklan Mmmmm..... why didn't I think of that one? Guess I was too eager finding the difficult way to solve the problem.
Jan 27 2022 02:22 PM
Once Lambda helper functions are available in 365, it should be possible to solve such problems with regular worksheet formulas.