Comparing Data in Columns A & B, using a Macro

andlearning

New Member
Joined
Feb 16, 2010
Messages
11
Hi and thank you so much for reading. So here's my situation: I have two columns of data, call them column A and column B - both of variable length. I want to compare each value from column A to column B, and every value in column B to column A. For those entries which do not have corresponding values in the other column, I want them to display on column C.

Below is an example, where Lime which is in column A is not in column B, and Kiwi which is in column B but not in A are both displayed in column C.

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }</style> <table style="border-collapse: collapse; width: 260pt;" border="0" cellpadding="0" cellspacing="0" width="260"> <col style="width: 65pt;" span="4" width="65"> <tbody><tr style="height: 15pt;" height="15"> <td style="height: 15pt; width: 65pt;" height="15" width="65"></td> <td style="width: 65pt;" width="65">Column A</td> <td style="width: 65pt;" width="65">Column B</td> <td style="width: 65pt;" width="65">Column C</td> </tr> <tr style="height: 15pt;" height="15"> <td style="height: 15pt;" height="15">Row 1</td> <td>Orange</td> <td>Apple</td> <td>Lime</td> </tr> <tr style="height: 15pt;" height="15"> <td style="height: 15pt;" height="15">Row 2</td> <td>Pineapple</td> <td>Orange</td> <td>Kiwi</td> </tr> <tr style="height: 15pt;" height="15"> <td style="height: 15pt;" height="15">Row 3</td> <td>Apple</td> <td>Kiwi</td> <td></td> </tr> <tr style="height: 15pt;" height="15"> <td style="height: 15pt;" height="15">Row 4</td> <td>Lime</td> <td>Pineapple</td> <td></td> </tr> <tr style="height: 15pt;" height="15"> <td style="height: 15pt;" height="15">Row 5</td> <td>Sugar</td> <td>Sugar</td> <td></td> </tr> </tbody></table>
The length of data in either columns is not set, and would like to have the flexibility for a macro to be able to know how long the column is and compare the entire length of each column. Probably a loop function until last element is compared.

I hope I've adequately described my problem. I have a preference for a macro but would be okay with excel Formulas. I tried using formulas but it typically involves more steps, copying and pasting since VLOOKUPS, MATCH, or ISNUMBER all give me matches not those elements that are not matching, and there's a bunch of N/As that I have to sort out.

In the above example, it would also be nice to know that "Lime" was unique to Column A and that "Kiwi" was unique to column B. Perhaps in D having it say "Unique in Column A" or "Unique in Column B" would be nice.

Thanks in advance, and please let me know if I can clarify anything.

- andlearning

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }</style>
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Code:
Sub Uniques()
    
    Dim cell As Range, Found As Range
    Dim counter As Long
    
    Application.ScreenUpdating = False
    
    Range("C:D").ClearContents
    counter = 1
    
    ' List uniques from column A that are not in column B
    For Each cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
        Set Found = Range("B:B").Find(cell, , , , , , False)
        If Found Is Nothing Then
            Range("C" & counter).Value = cell.Value
            Range("D" & counter).Value = "A"
            counter = counter + 1
        End If
    Next cell
    
    ' List uniques from column B that are not in column A
    For Each cell In Range("B1", Range("B" & Rows.Count).End(xlUp))
        Set Found = Range("A:A").Find(cell, , , , , , False)
        If Found Is Nothing Then
            Range("C" & counter).Value = cell.Value
            Range("D" & counter).Value = "B"
            counter = counter + 1
        End If
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
maybe
Code:
Sub diffcols()
Dim na As Long, nb As Long
Dim a As Range, b As Range
Dim e, r As Long, s As Long, c()
na = Cells(Rows.Count, "a").End(3).Row
nb = Cells(Rows.Count, "b").End(3).Row
ReDim c(1 To Application.Max(na, nb), 1 To 2)
Set a = [a1].Resize(na)
Set b = [b1].Resize(nb)
For Each e In a
    If IsError(Application.Match(e, b, 0)) Then
        r = r + 1
        c(r, 1) = e
    End If
Next e
For Each e In b
    If IsError(Application.Match(e, a, 0)) Then
        s = s + 1
        c(s, 2) = e
    End If
Next e
Cells(1, "c").Resize(Application.Max(r, s), 2) = c
End Sub
 
Upvote 0
Thanks AlphaFrog, this seems to work but does it require that the length of columns match? I added a few data points and change the length of data being compared and it didn't seem to work.

For example, I added starting from Row 5 to my initial query and when I ran the macro I received the following: which is missing "Sour" from column A...

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }</style> <table style="border-collapse: collapse; width: 325pt;" border="0" cellpadding="0" cellspacing="0" width="325"> <col style="width: 65pt;" span="5" width="65"> <tbody><tr style="height: 15pt;" height="15"> <td style="height: 15pt; width: 65pt;" height="15" width="65"></td> <td style="width: 65pt;" width="65">Column A</td> <td style="width: 65pt;" width="65">Column B</td> <td style="width: 65pt;" width="65">Column C</td> <td style="width: 65pt;" width="65">Column D</td> </tr> <tr style="height: 15pt;" height="15"> <td style="height: 15pt;" height="15">Row 1</td> <td>Orange</td> <td>Apple</td> <td>Lime</td> <td>A</td> </tr> <tr style="height: 15pt;" height="15"> <td style="height: 15pt;" height="15">Row 2</td> <td>Pineapple</td> <td>Orange</td> <td>Kiwi</td> <td>B</td> </tr> <tr style="height: 15pt;" height="15"> <td style="height: 15pt;" height="15">Row 3</td> <td>Apple</td> <td>Kiwi</td> <td>Sour 1</td> <td>B</td> </tr> <tr style="height: 15pt;" height="15"> <td style="height: 15pt;" height="15">Row 4</td> <td>Lime</td> <td>Pineapple</td> <td></td> <td></td> </tr> <tr style="height: 15pt;" height="15"> <td style="height: 15pt;" height="15">Row 5</td> <td>Sugar</td> <td>Sugar</td> <td></td> <td></td> </tr> <tr style="height: 15pt;" height="15"> <td style="height: 15pt;" height="15">Row 6</td> <td>Spice</td> <td>Spice</td> <td></td> <td></td> </tr> <tr style="height: 15pt;" height="15"> <td style="height: 15pt;" height="15">Row 7</td> <td>Sour</td> <td>Apple</td> <td></td> <td></td> </tr> <tr style="height: 15pt;" height="15"> <td style="height: 15pt;" height="15">Row 8</td> <td></td> <td>Sour 1</td> <td></td> <td></td> </tr> </tbody></table>
 
Upvote 0
Thanks Mirabeau, so column C is unique values from column A and column D are unique values from column A. Is there a way to continue with this code and have a column E that has the unique list of column c and column D combined?

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }</style> <table style="border-collapse: collapse; width: 325pt;" border="0" cellpadding="0" cellspacing="0" width="325"> <col style="width: 65pt;" span="5" width="65"> <tbody><tr style="height: 15pt;" height="15"> <td style="height: 15pt; width: 65pt;" height="15" width="65"></td> <td style="width: 65pt;" width="65">Column A</td> <td style="width: 65pt;" width="65">Column B</td> <td style="width: 65pt;" width="65">Column C</td> <td style="width: 65pt;" width="65">Column D</td> </tr> <tr style="height: 15pt;" height="15"> <td style="height: 15pt;" height="15">Row 1 </td> <td>Orange</td> <td>Apple</td> <td>Lime</td> <td>Kiwi</td> </tr> <tr style="height: 15pt;" height="15"> <td style="height: 15pt;" height="15">Row 2</td> <td>Pineapple</td> <td>Orange</td> <td>Sour</td> <td>Sour 1</td> </tr> <tr style="height: 15pt;" height="15"> <td style="height: 15pt;" height="15">Row 3</td> <td>Apple</td> <td>Kiwi</td> <td></td> <td></td> </tr> <tr style="height: 15pt;" height="15"> <td style="height: 15pt;" height="15">Row 4</td> <td>Lime</td> <td>Pineapple</td> <td></td> <td></td> </tr> <tr style="height: 15pt;" height="15"> <td style="height: 15pt;" height="15">Row 5</td> <td>Sugar</td> <td>Sugar</td> <td></td> <td></td> </tr> <tr style="height: 15pt;" height="15"> <td style="height: 15pt;" height="15">Row 6</td> <td>Spice</td> <td>Spice</td> <td></td> <td></td> </tr> <tr style="height: 15pt;" height="15"> <td style="height: 15pt;" height="15">Row 7</td> <td>Sour</td> <td>Apple</td> <td></td> <td></td> </tr> <tr style="height: 15pt;" height="15"> <td style="height: 15pt;" height="15">Row 8</td> <td></td> <td>Sour 1</td> <td></td> <td></td> </tr> </tbody></table>
 
Upvote 0
I know you have a few answers already, but I will post my solution also:
Code:
Sub CompareColumnA2B()
'Assign variables for Columns A - C
    aRow = Cells(Rows.Count, "A").End(xlUp).Row
    bRow = Cells(Rows.Count, "B").End(xlUp).Row
    cRow = 1
'Get Uniques from Column A
    For Each aVal In Range("A1:A" & aRow)
        If Application.WorksheetFunction.CountIf(Range("B1:B" & bRow), aVal) = 0 Then
            Cells(cRow, "C").Value = aVal
            Cells(cRow, "D").Value = "Unique from A"
            cRow = cRow + 1
        End If
    Next aVal
'Get Uniques from Column B
    For Each bVal In Range("B1:B" & bRow)
        If Application.WorksheetFunction.CountIf(Range("A1:A" & aRow), bVal) = 0 Then
            Cells(cRow, "C").Value = bVal
            Cells(cRow, "D").Value = "Unique from B"
            cRow = cRow + 1
        End If
    Next bVal
End Sub
 
Upvote 0
Thanks Datsmart, this seems to work except in the case of "Sour" from column A. This solution and others seem to get caught up with "Sour" from column A and "Sour 1" in column B - both of which are unique. Perhaps the space between the word Sour and the number 1 is negatively affecting the code?
 
Upvote 0
I added "Sour" and "Sour 1" to my test list and they both show up in the Unique listing.

Are they not displaying in Column C on your worksheet?
 
Upvote 0
You can have the results laid out however you like. No problem.

Does this modification do it?
Code:
Sub diffcols2()
Dim na As Long, nb As Long
Dim a As Range, b As Range
Dim e, r As Long, s As Long, c()
na = Cells(Rows.Count, "a").End(3).Row
nb = Cells(Rows.Count, "b").End(3).Row
ReDim c(1 To na + nb, 1 To 3)
Set a = [a1].Resize(na)
Set b = [b1].Resize(nb)
For Each e In a
    If IsError(Application.Match(e, b, 0)) Then
        r = r + 1
        c(r, 1) = e
        c(r, 3) = e
    End If
Next e
For Each e In b
    If IsError(Application.Match(e, a, 0)) Then
        s = s + 1
        c(s, 2) = e
        c(r + s, 3) = e
    End If
Next e
Cells(1, "c").Resize(r + s, 3) = c
End Sub
 
Upvote 0
datsmart, I stand corrected. I reran the macro and it does include "Sour" and "Sour 1" - thank you. I just need to make sure that I don't have spaces between the elements in either column. Testing what a blank cell in Column A would do when sandwiched between cells with values, it shows up as a blank cell in column C and says its unique to column A. Small problem to have in the grand scheme of things.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top