Sep 08 2022 09:33 AM
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.
Sep 08 2022 01:09 PM
@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.
Sep 08 2022 01:45 PM
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 |
Sep 08 2022 01:58 PM
To replicate a value from List B only when it occurs in a second List A
= IF(
COUNTIFS(ListA, ListB),
ListB,
"")
Sep 08 2022 02:00 PM - edited Sep 08 2022 02:17 PM
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
Sep 08 2022 02:17 PM
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