# VBA - compare 2 columns and return matching values in another column



## tikcr (Dec 20, 2022)

Hi All,
I am starting my journey with VBA, so sorry when this was already posted before, but I couldn't find satisfactionary response.
What I would like to do is to compare all values from Col 1 and Col 2 starting from the second row, and if the values match (if any of the value from Column 1 is in Column 2  or other way around, they should be displayed in 'Matching values'.


Column 1Column 2Matching values1QWE2AsD2asD2LLO1ZXc1qwe4ASZ1zXC

In this case, I would expect values 1QWE, 2ASD, 1ZXC to be displayed in Matching values column when I press the button 'Check' with assigned macro. I would highly appreciate your support!


----------



## alansidman (Dec 20, 2022)

```
Option Explicit
Option Compare Text

Sub Foo()
    Dim i As Long, lr As Long, j As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To lr
        For j = 1 To lr
            If Range("A" & i) = Range("B" & j) Then
                Range("C" & i) = Range("B" & j)
            End If
        Next j
    Next i
    MsgBox "completed"
End Sub
```


----------



## tikcr (Dec 21, 2022)

Thank you so much. It works, however when i delete the results of a check from 'Matching values', add some new values in col 1 and col 2 and try to run the script again, only msg box comes in but no results are then posted in the column 'Matching values'.


----------



## alansidman (Dec 21, 2022)

Should work.  Suppose you post your starting values and then show your new values in two different tables/ranges so we can test.  Please load your data using XL2BB.


----------



## tikcr (Dec 21, 2022)

Hello, thanks for your support. In the code I changed Range("C" & i) to Range("D" & i), so I am getting results in column D. 
By the way, is it possible to have the result of common values cell by cell (so if both values are in Column 1 and Column 2 then paste the common value in D2, D3, D4, etc, without empty cells in-between?)

Then what I tried after the result is visible, I deleted all results from Matching Values and I am using new data (will be copied manually from another excel sheet) but as an example, I would like to paste now new data to Column 2 from 'Paste data...'. 
After that I run the macro again and would like to get new 'Matching values' which come from comparison of Col 1 and Col 2. For some reason it does not work for me and only shows 1 common value between two columns which is wrong...

Here is the data I am using:


columns compare.xlsxABCDEFGH1Column 1Column 2Matching valuesafter the check, press button 'Clear' and delete all results from 'Matching values'Paste new data into Column 2 and check matching values2ZAU000016271ZAU000002917ZAU0000144743ZAU000002891ZAU000017097ZAU0000170974ZAU000002909ZAU000002917ZAU0000127425ZAU000002917ZAU000012742ZAU0000028916ZAU000002933ZAU000012718ZAU0000126017ZAE400000010ZAU000008096ZAU0000123538ZAU000008096ZAU000016271ZAU0000137169ZAU000008187ZAU000013716ZAU00000289110ZAU000008229ZAU000013724ZAU00001260111ZAU000016792ZAU000012346ZAU00001234612ZAU000012353ZAU00000809613ZAU000012551ZAU00000809614ZAU000012601ZAU00001235315ZAU000014011ZAU00001627116ZAU00001402917ZAU00001296518ZAU00001295719ZAU00001447420ZAU00001705521ZAU00001387222ZAU00000809623ZAU00001235324ZAU00001627125ZAU000002891Arkusz1


----------



## alansidman (Dec 21, 2022)

Cannot replicate your issue.  I delete the data in Columns A and D and then put new data in Column A.  No issues.
Here is the new code to show no blank spaces


```
Option Explicit
Option Compare Text

Sub Foo()
    Dim i As Long, lr As Long, j As Long, lr2 As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To lr
        For j = 1 To lr
        lr2 = Range("D" & Rows.Count).End(xlUp).Row
            If Range("A" & i) = Range("B" & j) Then
                Range("D" & lr2 + 1) = Range("B" & j)
            End If
        Next j
    Next i
    MsgBox "completed"
End Sub
```


----------



## jolivanes (Dec 22, 2022)

Another way if I understand your requirement right.

```
Sub Maybe()
Dim lr1 As Long, lr2 As Long, i As Long
lr1 = Cells(Rows.Count, 1).End(xlUp).Row
lr2 = Cells(Rows.Count, 2).End(xlUp).Row
    For i = 2 To lr2
        If WorksheetFunction.CountIf(Range("A2:A" & lr1), Range("B" & i)) <> 0 Then Cells(Rows.Count, 4).End(xlUp).Offset(1).Value = Range("B" & i).Value
    Next i
End Sub
```
Here I assumed Column B to be the shortest data Column. Change the references so you'll loop through the shortest column if different setup.


----------

