How to hide duplicate records but show them once

Lisa · 08-01-2005, 02:05 PM

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?

**barrfly** · 08-01-2005, 02:23 PM

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.

Duke Carey · 08-01-2005, 03:05 PM

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

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

"Lisa" wrote:

> 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?

Lisa · 08-01-2005, 03:05 PM

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.

"Duke Carey" wrote:

> See Chip Pearson's website for advice on dealing with dupes
>
> http://www.cpearson.com/excel/duplicat.htm
>
>
>
> "Lisa" wrote:
>
> > 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?

Debra Dalgleish · 08-01-2005, 04:05 PM

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

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

Lisa wrote:
> 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?

--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Lisa · 08-01-2005, 04:05 PM

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" wrote:

>
> 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.
>
>
> --
> barrfly
>
> Excel User - Energy markets
> ------------------------------------------------------------------------
> barrfly's Profile: http://www.excelforum.com/member.php...fo&userid=4141
> View this thread: http://www.excelforum.com/showthread...hreadid=391880
>
>

**barrfly** · 08-01-2005, 04:16 PM

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** · 08-01-2005, 04:26 PM

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.