formula

Copper Contributor

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

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

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.

AB
1332613326
1735617356
4551445514
 43143
 33138
3638636386
 1415
5478854788
92669266
 12851
1268712687
56845684
54725472
1977419774
 46907
 55386
5404454044
3815238152
 50981
4991049910

@melgra1965 

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

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

@melgra1965 

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

 

 

@melgra1965 

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