Solved

Flagging Duplicates in Excel

Posted on 2014-03-03
12
241 Views
Last Modified: 2014-03-14
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.
0
Comment
Question by:Bob Schneider
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 8

Assisted Solution

by:5teveo
5teveo earned 167 total points
ID: 39902224
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
0
 
LVL 12

Assisted Solution

by:tel2
tel2 earned 250 total points
ID: 39902227
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.
0
 

Author Comment

by:Bob Schneider
ID: 39902973
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.
0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

Author Comment

by:Bob Schneider
ID: 39902977
BTW it was written in Office 365 but I have tried copying it into Excel 2010 but to no avail.
0
 
LVL 32

Assisted Solution

by:Rob Henson
Rob Henson earned 83 total points
ID: 39903704
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.
0
 
LVL 12

Expert Comment

by:tel2
ID: 39904266
> Is there any way to remove the formula and keep the text?

Yes there is, BobbaBuoy.  Did you read first suggestion (from Steveo)?
0
 

Assisted Solution

by:Bob Schneider
Bob Schneider earned 0 total points
ID: 39904395
@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.
0
 
LVL 12

Assisted Solution

by:tel2
tel2 earned 250 total points
ID: 39904465
> 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?
0
 
LVL 8

Assisted Solution

by:5teveo
5teveo earned 167 total points
ID: 39904487
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.
0
 

Author Comment

by:Bob Schneider
ID: 39904498
@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!
0
 
LVL 12

Accepted Solution

by:
tel2 earned 250 total points
ID: 39904539
> 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.
0
 

Author Closing Comment

by:Bob Schneider
ID: 39928701
Very helpful!
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now