finding "like" part numbers

I am working with a database of 15,000 part numbers.

I have just come across a problem where we are finding that the same part number will appear a number of times.

All part numbers have been suffixed with an SM however some are suffixed with ASM, CSM, ESM and so on.

The base part number is 7 numbers ie:

1234567

However I am finding that some are duplicate numbers but with a changed suffix at the end. The same number may appear up to 5 times. Ie:

1234567SM
1234567ASM
1234567BSM
1234567CSM
1234567DSM

To make matters they have different $ values, and I have just been informed by our head office that some are service parts which should be on our database and some are production part numbers which should NOT appear on our database. (Pulls hair out and screams)
Now to get our head office to fix this and resubmit the files to me minus the the data I do not need is like trying to get blood from a stone.

So I need to seperate these parts from the sheet so that I can identify the parts that have additional "like" numbers. I have tried setting up a custom filter, but can get it to only filter the one set of values at a time (ie find all the parts with an ASM suffix, all the parts with a BSM suffix etc.) but want it only to find the part numbers that are repeated.
So if there is a part that has an ASM suffix, but does not appear again with any other suffix its not included.

I hope I am making sense.

Any help will be greatly appreciated.

TIA,
Leigh

Hi Leigh,

This might help (if I've understood you correctly!)

Assume your Part Nos are entered in cells A1:A15000

Enter the following formula in cell B1:

Please Login or Register  to view this content.

and copy it all the way down to cell B15000. This will display only the seven-digit part nos without any suffixes.

Now enter the following formula in cell C1:

Please Login or Register  to view this content.

and copy it all the way down to cell C15000. This will display "Duplicate No" beside any seven-digit part nos which appear more than once in the list - i.e. duplicates.

You can then sort columns A:C on column C to place all of the duplicate records at the end of the list where they can all be deleted in a single operation.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hows this
Use text to columns, fixed width to put the number and the suffix in different columns.
This gives us columns A and B
Then use Advanded Filter with a custom formula
C1 is blank and C2 is =(COUNTIF($A:$A,A2)=1)
C1:C2 will be the criteria range.

First copy the list to a different location. (Using C1:C2 as the criteria range). That stores the unique entries.
Then filter the list in place (same criteria) and delete the visible (unique entries).

That seperates your duplicates from the uniques.

I just wanted to say thanks for your help!!
My boss ended up deciding that it should not be our problem and we will wait for our head office to sort it out!

Hi Leapy,

Thanks for the feedback - looks like you've got the right kind of boss!

Best regards,

Greg M