Comparing Data in Columns A & B, using a Macro

[andlearning]

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]

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]

maybe

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]

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]

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]

I know you have a few answers already, but I will post my solution also:

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]

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]

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]

You can have the results laid out however you like. No problem.

Does this modification do it?

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]

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.