How to hide duplicate records but show them once

[Guest]

I'm trying to weed out duplicate records but I need to show them once. I
can't figure out how to do this without alot of formatting and deleting. Can
anyone help?

 

[Guest]

See Chip Pearson's website for advice on dealing with dupes

http://www.cpearson.com/excel/duplicat.htm

 

[barrfly]

if the data is in one column, highlight the column, the select Data ->
Filter -> Advanced Filter. Make sure the range is correct and select
the unique records only field. This will filter the list in place with
only one occurence of the records in the list.

 

[Guest]

Thanks Duke but this only tells me how to tag or highlight and I just want to
see each record once if it's listed once or 15 times I still only want it to
show once and can't figure this out.

 

[Debra Dalgleish]

You can use Conditional Formatting to hide the duplicates. There are
instructions here:

http://www.contextures.com/xlCondFormat03.html#Duplicate

 

[Guest]

Thanks for the suggestion, Unfortunately this will not show the item if it is
listed twice. It will only show those that are listed once.

 

[barrfly]

This shows each unique record only once. If a row has an occurence of
the same value 1, 2, 5 or 300 times, the resulting list will only show
each unique value only once. If it is not working for you, it could be
one of two things that I can think of off the top of my head.

1. is that the values are number values and are rounded to look the
same, but have different decimal tailing decimal values.

2. the values you are sorting are formulas and not "hard-coded" values.
The advanced filter looks at the cell contents (ie. hard coded values
or formulas) and determines uniqueness accordingly.

What type of information are you sorting? numbers, names? could there
be non-visible trailing spaces that you can not see?

 

[barrfly]

A simple way to do this with formulas would be to do the following.

1. sort the column that has the values you are evaluating.

2. in an adjacent column, set the first cell (even with the top value
in your list) equal to 1. so if your values are in column A, put the
"1" in column B.

3. below the "1" cell, (in this case B2) enter the formula
=IF(A2=A1,"x",1)

4. this will give you a list with lots of x and 1s. apply an
autofilter to this list and filter out only the 1s

Let me know how this works.