Matching Excel cells by column

Copper 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