Duplicates in Excel

Wearb

I have two columns of car reg numbers. Some of the columns will have the same numbers. I want to compare the two columns and highlight the duplicates. I then want to delete the duplicates and be left with only the unique numbers.

I have been trying to do this using the countif function, but I am not very good with it. Can any help me with this.

bnt

This could be an application for VLookup. If I assume your two columns are A1:A100 and B1:B100, then putting the following formula in C1:
=VLOOKUP(B1,$A$1:$A$100,1,FALSE)
This will either return the value in B1 if there's a duplicate, or #N/A if not. It works by searching for the value in B1 within the first column (A$1:$A$100). The 1 is the column number to search, and the FALSE means "no fuzzy matching" i.e. exact match only. You would copy the formula down (C1:C100), and anything but #N/A in C<n> means that the value in B<n> is a duplicate.

Wearb

Thanks for the reply. I have tried your suggestion, but without success. Perhaps I am doing something wrong.
I have two columns. Column A has 500 reg numbers. Column B has 270 reg numbers. I want to check to see how many of the reg numbers in column A are the same as the reg numbers in column B

Using your formula, I changed "$A$100" to "$A$500", because column A contains 500 entries. Have I done something wrong?

I would like to change from this;
123 456
234 567
456 123
345 789 where 123 and 456 are not matched

to
123 123
234 567
456 456
345 789

MathsManiac

What you've illustrated in your last post is not the same as what you described in your first post.

Your last post suggests that you have two columns, with the same number of entries in each column, and you just want to make sure that any matching numbers are displayed beside each other.

Is that correct? You have neither highlighted nor deleted the matching pairs, which is what you said you wanted to do.

It might be easier to help if you give a bit more of the context: do you ultimately want to finish up with just a single list of the unique entries? Are there other associated data in adjoining columns (e.g. names) that have to be kept beside their numbers?

If you just want to end up with a single list of all the unique numbers, you could just paste one column below the other and then filter out the duplicates. (Click on the help in Excel and check out: "Filter for unique values or remove duplicate values")

Wearb

I am sorry if my posts are confusing. I didn't think it mattered if the columns were of different length. I thought my last post would help explain what I needed, but obviously not.

The columns are of different length. My goal is to find the numbers in column B that match the numbers in A (the longer column), then delete the matched numbers in column B (column A is no longer needed at this stage) and be left with the numbers in column B that have no matches.
Thank you for your patience

ArmCandyBaby

Type

=IF(B1=IFERROR(VLOOKUP(B1,A:A, 1, FALSE), ""), "", B1)

In cell C1 and drag it down for the length of column B and see if that works. It might not since I think the IFERROR function is only compatible with later versions of Excel.

Wearb

You are correct. It gives a name error in excel 2003. Thanks for trying.

I have discovered a macro that will do it called "ConFormats". Thanks for your help. I never thought it would have been so difficult to do manually.

bnt

For starters, if you want numbers to line up, you should sort each column (using the normal Sort function). No point trying this kind of thing with the numbers in each column out of order.

You say "I want to check to see how many of the reg numbers in column A are the same as the reg numbers in column B": the formula I gave at the start will "flag" any duplicates. Once you have that, you can then count the "flags".

I don't know what you mean by "without success". That tells me absolutely nothing. If you want people to help you, you need to provide better descriptions of what IS happening, and what you are expecting.

Wearb

Sorry but it was not conformats macro, but ListCompare macro that does the job for me.

Thank you all for your help.

Wearb

bnt Thanks for bearing with me. "without success" meant that I got #N/A in all the cells in column C.
I have tried it again and I managed to get it working this time. I think it worked because I deleted the header row this time. No it wasnt that. I just tried it with headers in place and it still worked. Any I am sorted Thanks. I have saved the working sheet for future use.