Sep 08 2022 09:33 AM
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
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:
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:
Hopefully this helps get you started.
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.
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