Flagging Duplicates in Excel

Bob Schneider
Bob Schneider used Ask the Experts™
on
I have a field that is sorted...happens to contain email addresses...and I want to eliminate duplicates.  My thought was to create a new column and use =if(e1=e2,"duplicate","unique") and then sort by that column and delete the rows with the word "duplicate" in it.  It is not sorting.  Is it because of the formula rather than the cell value?  I tried formatting the column as text but that didn't seem to help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2012
Commented:
Copy and paste as values from original Worksheet into a new worksheet then perform actions you suggested...

That will work. No formula will be involved
I wouldn't have expected to have a problem with sorting a column which contains a formula.  But I could be wrong.

Please post the spreadsheet, complete with the dulicate/unique formulae.

If sorting formula fields is a problem, and you don't want to copy/paste values to a new spreadsheet, just copy/paste to an empty column in the same spreadsheet.
Bob SchneiderCo-Owner

Author

Commented:
I can't post the spread sheet because it is full of email addresses but I have tried everything that has been suggested and to know avail.  Is there any way to remove the formula and keep the text?  It appears that when I try to sort the column it re-applies the formula.
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Bob SchneiderCo-Owner

Author

Commented:
BTW it was written in Office 365 but I have tried copying it into Excel 2010 but to no avail.
Rob HensonFinance Analyst
Commented:
Apply Autofilter to the block of data. Set filter to show only "duplicate".

You can then select visible rows as a block and delete contents or delete rows.

Deleting contents will then leave blank rows which can then be removed by sorting.

There is also the Remove Duplicates function, depending on which version of Excel you are using.

Thanks
Rob H

EDIT:

After deleting rows remove the filter, the E1=E2 formula comparison will give #Ref for some rows because you have removed one of the comparotors. As it is only for getting rid of duplicates, i assume this formula will be deleted once finished with anyway.
> Is there any way to remove the formula and keep the text?

Yes there is, BobbaBuoy.  Did you read first suggestion (from Steveo)?
Bob SchneiderCo-Owner
Commented:
@tel2, I did read that but it didn't work.  I tried copying to different versions of Excel as well.  I will try robhenson's approach next.
> I did read that but it didn't work.
In what way did it not work?  Did nothing paste?  Did the result of the paste still contain formulae?  Did sorting the result not sort properly?

Did you try pasting into the same sheet, as I suggested?
Top Expert 2012
Commented:
If you paste as 'values' under 'Paste special' you should not have Email formatting issue.

Does not matter whether you are on current spreadsheet of current workbook or any other spreadsheet of any workbook.
Bob SchneiderCo-Owner

Author

Commented:
@tel2, it did the same as before...kept the formulas and then changed the values (duplicate or unique) based on their new location).  I did not try Paste special.  I will do that this evening.

I really appreciate all the help.  Thanks!
> I did not try Paste special.  I will do that this evening.
Yes, to "Copy and paste as values" (from Steveo's post), you do need to use Paste Special.
Bob SchneiderCo-Owner

Author

Commented:
Very helpful!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial