Home

Matching Excel cells by column

%3CLINGO-SUB%20id%3D%22lingo-sub-822904%22%20slang%3D%22en-US%22%3EMatching%20Excel%20cells%20by%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-822904%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I'm%20trying%20to%20do%20what%20I%20thought%20was%20a%20simple%20match%2C%20but%20it%20looks%20to%20be%20a%20little%20more%20complicated%20than%20I%20thought.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20two%20lists%2C%20each%20with%20their%20own%20column%20%26amp%3B%20respective%20ID%20%23.%20For%20example%2C%20%22%20%5BMicrosoft%5D%20%5B24591245%5D%20%22%2C%20using%20brackets%20to%20indicate%20cell.%20I%20want%20to%20match%20by%20the%20cell%20Microsoft%2C%20and%20I%20want%20the%20ID%20%23%20to%20move%20with%20the%20associated%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20I%20have%20a%20problem%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20A%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20C%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20D%3C%2FP%3E%3CP%3ERow%201%3A%20%5BMicrosoft%5D%20%5B28275721%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERow%207%3A%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5BMicrosoft%5D%20%5B9274395%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20possible%20to%20automate%20putting%20these%20two%20next%20to%20each%20other%2C%20using%20%22Microsoft%22%20as%20the%20match%2C%20and%20having%20the%20ID%20follow%20the%20match%3F%20I%20may%20want%20another%20cell%20to%20follow%20the%20matching%20cell%20as%20well.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20use%20condition%20formatting%20duplicate%20values%20to%20find%20the%20value%20I%20want%2C%20but%20sometimes%20there's%20duplicate%20accounts.%20I%20would%20like%20those%20to%20move%20but%20if%20they%20don't%20have%20a%20matching%20value%20that's%20fine%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20A%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20C%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20D%3C%2FP%3E%3CP%3ERow%201%3A%20%5BMicrosoft%5D%20%5B28275721%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERow%207%3A%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5BMicrosoft%5D%20%5B9274395%5D%3C%2FP%3E%3CP%3ERow%2060%3A%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5BMicrosoft%5D%20%5B9777772%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20look%20like%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20A%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20C%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20D%3C%2FP%3E%3CP%3ERow%201%3A%20%5BMicrosoft%5D%20%5B28275721%5D%26nbsp%3B%20%5BMicrosoft%5D%20%5B9274395%5D%3C%2FP%3E%3CP%3ERow%202%3A%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%5BMicrosoft%5D%20%5B9777772%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20possible%3F%20Or%20even%20part%20of%20this%20possible%3F%20Thanks%20in%20advance%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-822904%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMATCH%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-822994%22%20slang%3D%22en-US%22%3ERe%3A%20Matching%20Excel%20cells%20by%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-822994%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379557%22%20target%3D%22_blank%22%3E%40ddelise%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20use%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Ffilter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759%3FNS%3DEXCEL%26amp%3BVersion%3D90%26amp%3BSysLcid%3D1033%26amp%3BUiLcid%3D1033%26amp%3BAppVer%3DZXL900%26amp%3BHelpId%3Dxlmain11.chm60662%26amp%3Bui%3Den-US%26amp%3Brs%3Den-US%26amp%3Bad%3DUS%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EFilter%3C%2FA%3Efunction.%26nbsp%3B%3C%2FP%3E%3CP%3EApply%20filters%20on%20columns%20A%3AB%20and%20C%3AD%26nbsp%3B%20with%20condition%20column%20A%20and%20C%20%3D%22Microsoft%22%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-823964%22%20slang%3D%22en-US%22%3ERe%3A%20Matching%20Excel%20cells%20by%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-823964%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368896%22%20target%3D%22_blank%22%3E%40Kodipady%3C%2FA%3E%26nbsp%3Battaching%20a%20demo%20using%20Filter.%26nbsp%3B%20please%20check%20F2%20and%20H2%20for%20formula.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-824036%22%20slang%3D%22en-US%22%3ERe%3A%20Matching%20Excel%20cells%20by%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-824036%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F379557%22%20target%3D%22_blank%22%3E%40ddelise%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt's%20always%20better%20to%20upload%20a%20sample%20file%20to%20get%20a%20quick%20help%20and%20discard%20any%20confusion.%20This%20helps%20to%20visualize%20the%20end%20result%20you%20are%20trying%20to%20achieve.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyways%2C%20if%20you%20have%20access%20to%20dynamic%20array%20formulas%2C%20you%20can%20try%20the%20other%20solution%20above%2C%20otherwise%20you%20can%20try%20the%20following%20macro%20to%20get%20the%20desired%20output%20which%20you%20showed%20in%20your%20original%20post.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3ESub%20TransformData()%0AOn%20Error%20Resume%20Next%0ARange(%22A%3AD%22).SpecialCells(xlCellTypeBlanks).Delete%20shift%3A%3DxlUp%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Highlighted
ddelise
New Contributor

Hi, I'm trying to do what I thought was a simple match, but it looks to be a little more complicated than I thought.

 

I have two lists, each with their own column & respective ID #. For example, " [Microsoft] [24591245] ", using brackets to indicate cell. I want to match by the cell Microsoft, and I want the ID # to move with the associated cell.

 

For example, I have a problem like this:

            A                 B                 C                 D

Row 1: [Microsoft] [28275721]

 

Row 7:                                         [Microsoft] [9274395]

 

Is it possible to automate putting these two next to each other, using "Microsoft" as the match, and having the ID follow the match? I may want another cell to follow the matching cell as well.

 

I can use condition formatting duplicate values to find the value I want, but sometimes there's duplicate accounts. I would like those to move but if they don't have a matching value that's fine

 

            A                 B                 C                 D

Row 1: [Microsoft] [28275721]

 

Row 7:                                         [Microsoft] [9274395]

Row 60:                                       [Microsoft] [9777772]

 

Would look like

            A                 B                 C                 D

Row 1: [Microsoft] [28275721]  [Microsoft] [9274395]

Row 2:                                       [Microsoft] [9777772]

 

 

Is this possible? Or even part of this possible? Thanks in advance

 

 

3 Replies

@ddelise 

You can use Filter function. 

Apply filters on columns A:B and C:D  with condition column A and C ="Microsoft"  

 

 

@Kodipady attaching a demo using Filter.  please check F2 and H2 for formula. 

@ddelise 

 

It's always better to upload a sample file to get a quick help and discard any confusion. This helps to visualize the end result you are trying to achieve.

 

Anyways, if you have access to dynamic array formulas, you can try the other solution above, otherwise you can try the following macro to get the desired output which you showed in your original post.

 

Sub TransformData()
On Error Resume Next
Range("A:D").SpecialCells(xlCellTypeBlanks).Delete shift:=xlUp
End Sub
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies