Compare two rows of numbers. Remove the duplicates

**nicdyb** · 06-20-2012, 04:40 AM

Hi again

Is it possible to compare two rows of numbers:

Example:

Please Login or Register  to view this content.

And compare it with

Please Login or Register  to view this content.

And then remove the numbers that are identical, leaving only behind numbers that dont occur in BOTH.

I tried using the function "Remove Duplicates" didnt work that well, as it just compared each line to the other column like this; "Compare A12 to B12". I want it to compare the whole column and search for the exact same number in both columns.

Thanks, I feel like this is a tricky one :P

**JBeaucaire** · 06-20-2012, 04:57 AM

This macro will do it... assuming:

List1 is column A
List2 is column B
Columns D and H are empty and can be used

Please Login or Register  to view this content.

How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.

**abousetta** · 06-20-2012, 04:58 AM

Hello and welcome to the forum,

There are several ways to accomplish this, but there are several factors to determine the best opiton for you.

1) How long are your real lists?
2) Is there any associated data on the same column that will also be deleted or is the whole data one column?
3) Is this a one-time issue or will you be repeating this on a regular occasion?

**nicdyb** · 06-20-2012, 05:17 AM

My real list is about 200 cells.

I am comparing a invoice (all items charged the customer) with what has been manually put into our bills-system. I want to find out what misses in the invoice that is present in the bill-system, and also what is in invoice and has not been entered into the bill-system. Could call it a two way comparison.

The numbers are sort of "order ID's" meaning column A is all orders from invoice, column B is all from bill-system.

I will most likely be using this on a regular basis.

**abousetta** · 06-20-2012, 05:31 AM

JB's solution should work well in this situation.

**Fotis1991** · 06-20-2012, 05:31 AM

Hi

Another option not using VBA,is this.

Name your Lists as List1 & List2

Then in C2(C1, is the title or empty), use this Array formula(Control+Shift+Enter) not just Enter and copy down,

=IFERROR(IFERROR(INDEX(List1, MATCH(0, COUNTIF($C$1:C1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($C$1:C1, List2), 0))), "")

Is this, works for you?

**JBeaucaire** · 06-20-2012, 06:00 AM

If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.

**martindwilson** · 06-20-2012, 06:04 AM

or use advanced filter!

**nicdyb** · 06-20-2012, 06:08 AM

JB's solution worked perfectly, thanks for all the help guys

Compare two rows of numbers. Remove the duplicates

LinkBack

Thread Tools

Rate This Thread

Display

Compare two rows of numbers. Remove the duplicates

Re: Compare two rows of numbers. Remove the duplicates

Re: Compare two rows of numbers. Remove the duplicates

Re: Compare two rows of numbers. Remove the duplicates

Re: Compare two rows of numbers. Remove the duplicates

Re: Compare two rows of numbers. Remove the duplicates

Re: Compare two rows of numbers. Remove the duplicates

Re: Compare two rows of numbers. Remove the duplicates

Re: Compare two rows of numbers. Remove the duplicates

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions