Deleting duplicate entries

[J. Gutierrez]

Hi everyone,

I have a list with several thousand numbers (cancer morphology codes, many
repeats) with their corresponding text name in the adjacent column. How can I
delete all the repeats in the worksheet and be left with one of each number?

Example...This is what I have now (no header column, data sorted by Column
A, ascending values)

A B
1 8001 xxxxxx
2 8001 xxxxxx
3 8002 yyyyyy
4 8002 yyyyyy
5 8002 yyyyyy
6 8003 zzzzzz
7 8003 zzzzzz

and this is what I would like to end up with...

A B
1 8001 xxxxxx
2 8002 yyyyyy
3 8003 zzzzzz

Any suggestions?

Thanks for everyones help.

JG

[Michael]

Hi J. Try out Chip Pearson's site: http://www.cpearson.com/excel/duplicat.htm
--
Sincerely, Michael Colvin


"J. Gutierrez" wrote:

> Hi everyone,
>
> I have a list with several thousand numbers (cancer morphology codes, many
> repeats) with their corresponding text name in the adjacent column. How can I
> delete all the repeats in the worksheet and be left with one of each number?
>
> Example...This is what I have now (no header column, data sorted by Column
> A, ascending values)
>
> A B
> 1 8001 xxxxxx
> 2 8001 xxxxxx
> 3 8002 yyyyyy
> 4 8002 yyyyyy
> 5 8002 yyyyyy
> 6 8003 zzzzzz
> 7 8003 zzzzzz
>
> and this is what I would like to end up with...
>
> A B
> 1 8001 xxxxxx
> 2 8002 yyyyyy
> 3 8003 zzzzzz
>
> Any suggestions?
>
> Thanks for everyones help.
>
> JG

[Nikki]

you can also create a Pivot table to elimiate the duplicates.

"J. Gutierrez" wrote:

> Hi everyone,
>
> I have a list with several thousand numbers (cancer morphology codes, many
> repeats) with their corresponding text name in the adjacent column. How can I
> delete all the repeats in the worksheet and be left with one of each number?
>
> Example...This is what I have now (no header column, data sorted by Column
> A, ascending values)
>
> A B
> 1 8001 xxxxxx
> 2 8001 xxxxxx
> 3 8002 yyyyyy
> 4 8002 yyyyyy
> 5 8002 yyyyyy
> 6 8003 zzzzzz
> 7 8003 zzzzzz
>
> and this is what I would like to end up with...
>
> A B
> 1 8001 xxxxxx
> 2 8002 yyyyyy
> 3 8003 zzzzzz
>
> Any suggestions?
>
> Thanks for everyones help.
>
> JG

[grahammal]

Try this for size, works for me.

For uY = 1 To 6 'Spin thru 6 times to get them all
Qq = Application.CountA(ActiveSheet.Range("B:B")) 'Get row count before beginning
For oZ = 2 To Qq 'Main loop
If Cells(oZ, 1) = Cells(oZ - 1, 1) Then 'Check for dups
Cells(oZ, 1).Select 'Select the cell
Selection.Delete Shift:=xlUp 'Delete
Qq = Qq - 1 'Change loop criteria because cell gone
End If 'End
Next oZ 'Inside loop
Next uY 'Outside loop