Forum Discussion

troyyy's avatar
troyyy
Copper Contributor
Apr 27, 2021

Align duplicates from two columns to same rows in Excel

Hello,

 

I have the following situation:

 

I have data (Full Name, Column A) that needs to be aligned with other Data from Column C on (Full Name and beyond).

 

I did a very similar post recently. But the formula doesn't work in this case.

https://techcommunity.microsoft.com/t5/excel/align-duplicates-from-two-columns-to-same-rows-in-excel/m-p/2273761#M96440

 

 

Column AColumn BColumn CColumn DColumn EColumn F                  
Full Name Full NameEmailVerify StatusFirst NameLast NameFull NameUser SocialJob PositionProspect LocationAdd DateCompany NameCompany UrlCompany SocialCompany SizeCountryLocationStateCityIndustry   
Howard Kogan Howard Kogansecret@underscore.vcnot validHowardKoganHoward Koganhttps://www.linkedin.com/in/hkoganCore MemberBoston. Massachusetts. United States2021-04-26 13:04:10Underscore VChttp://www.underscore.vchttps://www.linkedin.com/company/1079178611-50United StatesBoston. Massachusetts. United StatesMABostonVenture Capital & Private Equity  
Pat Flynn Todd Gettelfingersecret@OrbitMedia.comunknownToddGettelfingerTodd Gettelfingerhttps://www.linkedin.com/in/toddgettelfingerChief Executive OfficerChicago. Illinois. United States2021-04-26 13:04:10Orbit Media Studios. Inc.https://www.orbitmedia.comhttps://www.linkedin.com/company/21899411-50United StatesChicago. Illinois. United StatesILChicagoMarketing and Advertising  
Todd Gettelfinger KEVIN INDIGsecret@kevin-indig.comnot validKEVININDIGKEVIN INDIG   2021-04-26 13:04:10Kevin-indighttp://kevin-indig.com          
Matthew WoodwardMatthew Woodwardsecret@MatthewWoodward.co.ukvalidMatthewWoodwardMatthew Woodward  2021-04-26 13:04:10Matthew Woodwardhttps://www.matthewwoodward.co.uk/https://www.linkedin.com/company/117168181-10United KingdomTonbridge. United KingdomEnglandTunbridge WellsInternet   
                      
                      

 

I want that it looks like this:

 

Column AColumn BColumn CColumn DColumn EColumn F                  
Full Name Full NameEmailVerify StatusFirst NameLast NameFull NameUser SocialJob PositionProspect LocationAdd DateCompany NameCompany UrlCompany SocialCompany SizeCountryLocationStateCityIndustry   
Howard Kogan Howard Kogansecret@underscore.vcnot validHowardKoganHoward Koganhttps://www.linkedin.com/in/hkoganCore MemberBoston. Massachusetts. United States2021-04-26 13:04:10Underscore VChttp://www.underscore.vchttps://www.linkedin.com/company/1079178611-50United StatesBoston. Massachusetts. United StatesMABostonVenture Capital & Private Equity   
Pat Flynn Pat Flynnsecret@smartpassiveincome.comvalidPatFlynnPat Flynn   2021-04-26 13:04:10Smartpassiveincomehttp://smartpassiveincome.com          
Todd Gettelfinger Todd Gettelfingersecret@OrbitMedia.comunknownToddGettelfingerTodd Gettelfingerhttps://www.linkedin.com/in/toddgettelfingerChief Executive OfficerChicago. Illinois. United States2021-04-26 13:04:10Orbit Media Studios. Inc.https://www.orbitmedia.comhttps://www.linkedin.com/company/21899411-50United StatesChicago. Illinois. United StatesILChicagoMarketing and Advertising   
Matthew WoodwardMatthew Woodwardsecret@MatthewWoodward.co.ukvalidMatthewWoodwardMatthew Woodward  2021-04-26 13:04:10Matthew Woodwardhttps://www.matthewwoodward.co.uk/https://www.linkedin.com/company/117168181-10United KingdomTonbridge. United KingdomEnglandTunbridge WellsInternet   
Matthew Barby                       
Mari Smith Mari Smithsecret@MariSmith.comvalidMariSmithMari Smithhttps://www.linkedin.com/in/marismithKeynote Speaker | San Diego. California. United States2021-04-26 13:04:10Mari Smith International. Inc.http://marismith.comhttps://www.linkedin.com/company/214037111-50United StatesSan Diego. California. United StatesCaliforniaSan DiegoProfessional Training & Coaching   
Kevin Indig KEVIN INDIGsecret@kevin-indig.comnot validKEVININDIGKEVIN INDIG   2021-04-26 13:04:10Kevin-indighttp://kevin-indig.com          

 

The Full Name of a person in Column C + all extra Data is aligned with the Full Name in Column A.

 

I uploaded a sample excel file.

 

Which formula would work here?

 

Cheers

Torge

 

1 Reply

  • troyyy 

    I'd run this macro:

    Sub Shuffle()
        Dim r As Long
        Dim m As Long
        Dim rng As Range
        Application.ScreenUpdating = False
        m = Range("A" & Rows.Count).End(xlUp).Row
        Range("C2:U" & m).Insert Shift:=xlShiftDown
        For r = 2 To m
            Set rng = Range("C:C").Find(What:=Range("A" & r).Value, LookAt:=xlWhole)
            If Not rng Is Nothing Then
                rng.Resize(1, 19).Cut Destination:=Range("C" & r)
            End If
        Next r
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
    End Sub

Resources