Forum Discussion
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
To replicate a value from List B only when it occurs in a second List A
= IF( COUNTIFS(ListA, ListB), ListB, "")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.
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 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
