New Contributor

# formula

I need to match 2 columns. I need to leave blank spaces when they do not match.

example: column a     column b

105                105

110

113                113

115                 115

120

125                125

Can someone tell me how to accomplish this? I am not very good with formulas in Excel so I am unable to figure it out.

5 Replies

# Re: formula

@melgra1965 it's unclear what two columns you are comparing against.

The simple answer based on what you've shown is:

=IF(A1=B1,"Match","")

This formula would reside in column C.

However, if you are trying to see if the value in column A is in another list or on another tab then obviously a different formula is required such as:

=IF(ISNUMBER(MATCH(A1,G:G,0)),A1,"")

Hopefully this helps get you started.

Dexter

This assumes the list of values you are trying to match against is in column G.  If it's on another tab simply replace G:G with the tab and column where the list resides.

# Re: formula

DexterG_III  I want to match column to column b, row by row.  If they do not match i want an empty space left so I end with the same number of rows in each column.  In column A I only have 800 numbers, in column B I have 1500.  I want to column A spread out to match column B so they both have 1500 rows when done.

 A B 13326 13326 17356 17356 45514 45514 43143 33138 36386 36386 1415 54788 54788 9266 9266 12851 12687 12687 5684 5684 5472 5472 19774 19774 46907 55386 54044 54044 38152 38152 50981 49910 49910

# Re: formula

To replicate a value from List B only when it occurs in a second List A

``````= IF(
COUNTIFS(ListA, ListB),
ListB,
"")``````

# Re: formula

Using your last example, you can run the macro below. But be very careful: if there is a value in column A that does not occur in column B, the code will go into an endless loop.

``````Sub MatchColumns()
Dim r As Long
Application.ScreenUpdating = False
Do
Do While Range("A" & r).Value <> Range("B" & r).Value
Range("A" & r).Insert Shift:=xlShiftDown
r = r + 1
If Range("B" & r).Value = "" Then Exit Do
Loop
r = r + 1
Loop Until Range("B" & r).Value = ""
Application.ScreenUpdating = True
End Sub``````

# Re: formula

A safer version:

``````Sub MatchColumns()
Dim r As Long
Dim rng As Range
Application.ScreenUpdating = False
r = 1
Do
Set rng = Range("B" & r & ":B" & Rows.Count).Find(What:=Range("A" & r).Value, LookAt:=xlWhole)
If rng Is Nothing Then
MsgBox "The value of A" & r & " was not found", vbCritical
Exit Do
ElseIf rng.Row = r Then
r = r + 1
Else
Range("A" & r).Resize(rng.Row - r).Insert Shift:=xlShiftDown
r = rng.Row + 1
End If
Loop Until Range("B" & r).Value = ""
Application.ScreenUpdating = True
End Sub``````