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>
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,166
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
 

mirabeau

Banned user
Joined
Nov 4, 2010
Messages
2,075
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
 

andlearning

New Member
Joined
Feb 16, 2010
Messages
11
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>
 

andlearning

New Member
Joined
Feb 16, 2010
Messages
11
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>
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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
 

andlearning

New Member
Joined
Feb 16, 2010
Messages
11
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?
 

Datsmart

Well-known Member
Joined
Jun 19, 2003
Messages
7,985
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?
 

mirabeau

Banned user
Joined
Nov 4, 2010
Messages
2,075
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
 

andlearning

New Member
Joined
Feb 16, 2010
Messages
11
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.
 

Forum statistics

Threads
1,082,155
Messages
5,363,468
Members
400,739
Latest member
Siopsy

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top