Find duplicates within worksheet

  • Thread starter Thread starter Charlene
  • Start date Start date
C

Charlene

I think a lookup of some kind could be used on this but I
can't figure it out. I have two fields:

A B
768 PTP-0012~PTP-0016~SOTC-0033~SOTC-0110
379 PTP-0012~PTP-0016~SOTC-0038
460 PTP-0012~PTP-0016~SOTC-0055~SOTC-0078
112 PTP-0012~PTP-0016~SOTC-0078
350 PTP-0012~PTP-0016~SOTC-0078
3048 PTP-0012~PTP-0016~SOTC-0078
601 PTP-0012~PTP-0072~PTP-0077

I need only values where column B has duplicates, the
other rows can be deleted. I would like to see the
corresonding column A. So I would like to see the
following:

112 PTP-0012~PTP-0016~SOTC-0078
350 PTP-0012~PTP-0016~SOTC-0078
3048 PTP-0012~PTP-0016~SOTC-0078

This can't be done in Access because column B is bigger
than 255 chars. Please let me know if there is anything
that can be done.

Thank you!
Charlene
 
Charlene
You can put this formula in C1

=COUNTIF(B:B,B1

and copy down to the end of your data. Double clicking the fill handle will do this automatically

You can then sort on column C and select only items with a 2 or higher

Good Luck
Mark Graesse
(e-mail address removed)

----- Charlene wrote: ----

I think a lookup of some kind could be used on this but I
can't figure it out. I have two fields

A
768 PTP-0012~PTP-0016~SOTC-0033~SOTC-011
379 PTP-0012~PTP-0016~SOTC-003
460 PTP-0012~PTP-0016~SOTC-0055~SOTC-007
112 PTP-0012~PTP-0016~SOTC-007
350 PTP-0012~PTP-0016~SOTC-007
3048 PTP-0012~PTP-0016~SOTC-007
601 PTP-0012~PTP-0072~PTP-007

I need only values where column B has duplicates, the
other rows can be deleted. I would like to see the
corresonding column A. So I would like to see the
following

112 PTP-0012~PTP-0016~SOTC-007
350 PTP-0012~PTP-0016~SOTC-007
3048 PTP-0012~PTP-0016~SOTC-007

This can't be done in Access because column B is bigger
than 255 chars. Please let me know if there is anything
that can be done

Thank you
Charlen
 
Thank you! That seemed to work for what I wanted... just
a little longer :)

Charlene
-----Original Message-----
Charlene,
You can put this formula in C1:

=COUNTIF(B:B,B1)

and copy down to the end of your data. Double clicking
Click to expand...
the fill handle will do this automatically.
 
Back
Top