Trouble with merging/consolidating

%3CLINGO-SUB%20id%3D%22lingo-sub-3073867%22%20slang%3D%22en-US%22%3ETrouble%20with%20merging%2Fconsolidating%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3073867%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20like%20to%20merge%2Fdelete%20any%20duplicates%20in%20columns%20A%20and%20B%20and%20merge%20any%20%22X%22s%20in%20columns%20C-F%20all%20into%20one%20row%20for%20each%20person.%20I've%20tried%20so%20many%20functions%20at%20this%20point%20after%20searching%20and%20nothing%20seems%20to%20work%20for%20what%20I%20am%20trying%20to%20do.%20Any%20help%20would%20be%20greatly%20appreciated.%20I%20thought%20about%20doing%20it%20manually%20but%20with%204000%2B%20entries%20it%20would%20just%20be%20way%20to%20time%20consuming.%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22excel%20snap.png%22%20style%3D%22width%3A%20749px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F342940iC707B0CDDE988176%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22excel%20snap.png%22%20alt%3D%22excel%20snap.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3073867%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3073910%22%20slang%3D%22en-US%22%3ERe%3A%20Trouble%20with%20merging%2Fconsolidating%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3073910%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1288771%22%20target%3D%22_blank%22%3E%40ScottF12%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERun%20this%20macro%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20MergeAndCombine()%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20Dim%20c%20As%20Long%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20Dim%20rng%20As%20Range%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20m%20%3D%20Cells(Rows.Count%2C%201).End(xlUp).Row%0A%20%20%20%20For%20r%20%3D%20m%20To%202%20Step%20-1%0A%20%20%20%20%20%20%20%20If%20Cells(r%20-%201%2C%201).Value%20%3D%20Cells(r%2C%201).Value%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20For%20c%20%3D%203%20To%206%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Cells(r%20-%201%2C%20c).Value%20%3D%20Cells(r%20-%201%2C%20c).Value%20%26amp%3B%20Cells(r%2C%20c).Value%0A%20%20%20%20%20%20%20%20%20%20%20%20Next%20c%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20rng%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Set%20rng%20%3D%20Cells(r%2C%201)%0A%20%20%20%20%20%20%20%20%20%20%20%20Else%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Set%20rng%20%3D%20Union(Cells(r%2C%201)%2C%20rng)%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20Next%20r%0A%20%20%20%20rng.EntireRow.Delete%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
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

@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

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

Riny_van_Eekelen_0-1643292942977.png

 

 

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

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.

@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

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

@ScottF12 

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

image.png