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>

 

[andlearning]

Thanks mirabeau, this also does it. Thank you. Same issue as datsmarts where it counts empty cells as uniques, but not a big deal at all. Thank you all for the swift contributions!

 

[mirabeau]

Thanks mirabeau, this also does it. Thank you. Same issue as datsmarts where it counts empty cells as uniques, but not a big deal at all. Thank you all for the swift contributions!

empty cells as uniques are v easy to omit.

Just put in the line

Cells(1, "c").Resize(r + s, 3).SpecialCells(4).Delete xlUp

immediately before the End Sub in my last code.