Need to find duplicates in Excel spreadsheet 1

[SethP]

I have a spreadsheet that list several different pieces of data. The spreadsheet looks as follows:
Cust Serial Date Amount Type

123 0656020652501 20060721 $115.82 P
123 0656020653011 20060724 ($40.12)X
123 0656020653011 20060721 $40.12 P
123 0656020658301 20060721 $10.18 P
123 0656020664931 20060724 $0.00 X
123 0656020664931 20060721 $0.00 P

The spreadsheet is 16,000+ records. As you can see several of the serial numbers are matches. I need to create an output that only gives me the serial numbers that are unique.
Any ideas?

Thanks for your help-
SethP

 

[SkipVought]

Hi,

So what do you do with the data elements that are not equal
[tt]
123 0656020653011 20060724 ($40.12)X
123 0656020653011 20060721 $40.12 P
[/tt]
How would these rows be displayed?

Skip,
[sub]
[red][/red]
[/sub]

 

[AnotherHiggins]

First question. Let's look at these two records:
[tt]Cust Serial Date Amount Type
123 0656020664931 20060724 $0.00 X
123 0656020664931 20060721 $0.00 P[/tt]

Both have the same Serial number. If eliminating one of the records, which Date, Amount and Type would you want returned?

If you just want to create a list of unique ID numbers without any of the rest of the info, copy 'n' paste that column into an empty worksheet. Go to Data > Filter > Advanced Filter > Unique Records Only > OK.

(PS - to get a table to look right when posting to Tek-Tips, use the [tt] tags around the table data.)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SethP]

Thanks John. I tried the unique records only, but that didn't create the output I need.
I need the only serial numbers that are present in the list once, those that are duplicated, and their related information I do not need.
Any ideas from here? I tried to think of a way to do this in access, but again, I can't think of a way to filter the list and only output serial numbers shown once.

 

[DirkStruan]

See Data/Autofilter/Advanced Autofilter and check the unique records only box. The first range selection should be your list/column of Serial numbers, make sure this range is one column wide.

If you need the list to be copied elsewhere you can tell it where to paste it under the 3rd range, if you just need to view it by hiding the duplicate rows, don't bother.

D

 

[AnotherHiggins]

In a new column, type this:

=countif(B:B,B2)

Drag that formula down.

Now sort the whole thing on that new column. All the 1's will be together - those serial numbers only appear once. You can delete all 2s, 3s, etc.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SethP]

Perfect. Thanks all!