» Find duplicates items in combine columns in Microsoft Excel

Have a sorted single column of about 600 cells, with alpha data (stock symbols).

Each time updated, the column contains duplicate entries....about 25 to 50 stocks are listed two or more times.

I manually sort the column. Then must tediously go through, eyeball the column and delete the duplicate rows.

Now, in some sort utilities external to Excel, you can specify that duplicates be dropped.

Is there a sort function or sort formula that can sort such a column in Excel, dropping duplicates/extra rows or cells (or any number of multiple instances of a value)? I do want to keep the first instance of each value, just not the subsequent duplicates.

Seems like such an easy thing, but absolutely cannot find anyone who has a solution!

Thanks.

Hi,

Could you please let me know the formula to check for duplicates from different sheets of the same Workbook. It would be great if you could give me a formula to prevent duplicate entries in different sheets.

Have a nice day.

Great, but is there also a way of it automatically dropping duplicates/extra rows or cells (or any number of multiple instances of a value)? I do want to keep the first instance of each value, just not the subsequent duplicates.

This will require you to insert a column next to the data you wish to sort, but it will provide you a list of the unique items in a range.

From John Walkenbach's "Microsoft Excel 2000 Formulas" (www.j-walk.com), p. 356

"If you have a single-column range named Data, the following array formula returns a list of the unique items in the range:

{=INDEX(Data,SMALL(IF(MATCH(Data,Data,0)=ROW(INDIRECT("1:"&ROWS(Data))),MATCH(Data,Data,0),""),ROW(INDIRECT("1:"(Data)))))}

Play around with it, and don't forget to enter it as an array. The formula is looking for unique items, and then placing them into a contiguous list without blank rows inbetween. If returning blank rows doesn't bother you, IF(COUNTIF($A$1:A1,A1),A1,"") copied down the inserted column next to your data will also work.

Hi,

Kindly advise me the formula for finding the duplicates in same work book but different spread sheets.

For ex. I have a work book which has more than 10 sheets ,if i enter any data which is present in any of the other sheet it should pop up a duplicate .

and also another doubt,

For ex.i have 8 to 9 colums to be filled in mandatory ,if i do not fill any one of the colum the excel should give a message box saying pls fill this colum .

How could I find duplicates in a colum of cells ( orders numbers). ex:
123456
123456
897543
789456

Could anyone please give an idea where can I find answers or a clue to my question about finding duplicates
in a column of cells.
Thanks a million for your help.

The posted answer doesn't seem correct to me.
Shouldn't the formula entered in E1 be: =IF(COUNTIF($C$1:$C$12,C1)>1,"Duplicate","Unique")?
Change: $C$1:C1 -> $C$1:$C$12
Even the screenshot shows incorrect results.

Hi Humberto,

If your order numbers are in column A (from A1 down), then enter the following in B1:

=countif(A:A,A1)

Copy that down as far as the bottom of your data.

It will tell you how many entries in the list in column A are the same as the one it refers to.

Hopefully most will be '1', so you might want to filter the list, and just show entries in column B that are greater than 1 to find the errors.

Hope that helps,

Alan.

Hi Coreen,

Depends on what you want to do!

The formula stated in the tip will flag the first time that a duplicate entry appears. This is useful if a dataset is being populated, and you want to know if someone (or something) enters a duplicate. The original entry will not be flagged since it is (presumed to be) correct.

Your forumula is similar to the one I posted above. This catches any duplicates. Logically, if one entry has a 'two' beside it (two copies of that entry) then another must also have a 'two' beside it (the other member of the pair).

Both are useful, depending on what you want to do.

Alan.

Is it possible to have excel highlite a known set of numbers in a work sheet with many rows and colums of variable numbers

Hi Geoff,

If you mean you want to highlight a specific number (or any number within two bounds), then yes.

Just use conditional formatting under the Format menu.

Alan.

Gday Allan,

Thanks for your reply.

Conditional formatting will only go to three numbers in my Excel, is there any way of finding and highlighting five specific numbers at once?

Geoff.

Hi Geoff,

Not sure I understand.

Do you mean that you can only have three alternative conditional formats? If so, then I think that is you lot without getting into a VBA solution.

However, if you mean that you want to apply one format to any occurance of any of five numbers, then you can definately do that.

Probably best to use a 'Formula Is' type conditional format, and construct it using OR statements:

Select cell A1 (say), then enter the conditional format:

=(OR(A1=12,A1=23,A1=45,A1=56,A1=96))

Replacing 12,23,45,56, and 96 with whatever you are looking for.

Then copy the *formatting* from cell A1 to whatever range you want to check.

HTH,

Alan.

The statement given:
=IF(COUNTIF($C$1:C1,C1)>1,"Duplicate","Unique")

Is fine for a small run of a few hundred, but when you do 53,000 rows presorting of what you want is quicker.

By this I mean if you have to remove duplicate phone numbers from a table in Excel.
A B C
15131234567 John Doe
15131234567 John Smith
15131255567 Jane Doe
15131234567 Jane Doe
15135551234 Jane Doe
15135554444 John Doe
15135554343 John Smith
15135551122 John Smith

Names can be duplicate because there can be lots of John Smiths that are not the same person or the same person can have more than one phone line. But there can only be one phone number used period. You want to remove Duplications of that phone number or find out which names have the same phone number so you can resolve which one is correct.

The Pre Sort would be sorting the Phone numbers first with a regular sort.

Then use a Simple function of:

=IF(A1=A2,"Duplicate", "Unique")

This checks the current row against the next row.

Trust me when doing 53,000 + rows it was a difference of 3 seconds for doing about 50,000 comparisons verses 1.5 Billion (with a B) comparisons. That’s 200 times difference. Oh and it will have to go back through your 600 second I.E. 10 minute computation every time you sort because you have automatic calculation on.

But if you are running this on someone else’s computer watching a 2.4Ghz with 512megs of ram fall over and crawl is priceless.

dear sir
as we r entering data in cell A:A or we r going to enter any duplicate record in it, can it possible excel give us response about duplicate data.

Hi Alok,

In what way is your query different from Humberto's above (see my reply of August 13, 2003 5:28 PM EST).

Please can you specify how or why that solution does not work for you.

Thanks,

Alan.

Adding to Bob Hobbs' tip:

=IF(A1=A2,"Duplicate", "Unique")

Copy the column where the above formula is entered
Paste Special (Value) back to the same column
Sort on this column

All the dupes are at the beginning; simply select and Delete all duplicates. Easy even for large files.