Please Help With Duplicates

D

Dee

I actually need some assistance with two things.

#1. How do i locate duplicates in a single colum on a single sheet? For
example i have a file with over over 10, 000 customers. I want to do a
duplicate search ONLY on the email column. How can i get excel to
identify duplicates in that column only.

#2. These customers have expire from my program and i have dates that
they will expire. Is there a formula that can be implemented to change
it from a false to a true after the date is reached? So if they expire
tommorrow,,,,, it changes from false to true on its own with out me
mannually doing it?
 
D

Dave Peterson

#1. I'd insert another column to the right of the data and use a formula like:

=countif(a:a,a2)

Then I could filter on that column to show values greater than 1 (and decide
which to keep and which to delete).

#2. You could put a common date in a cell and check that against all dates:
=if(b3>$a$1,"after date in A1","before (or equal) date in A1"

You can also check against the current date:
=if(b3>today(),"after today","before today")

But I'm not sure I'm getting the question right (and probably not the answer!).
 
G

Gary''s Student

Use conditional formatting to identify duplicates within a column:


1. select A1
2. pull-down Format > Conditional Formatting...
3. select Equation Is and enter:
=(COUNTIF(A:A,A1)>1)
select a distinctive format
4. select A1 again and copy
5. select the rest of the cells in column A and do a paste/special format


All cells with duplicated data will display your format.
 
Top