Ignoring text data while using numerical data when searching for duplicates

[KClaps]

For some context, I work at a school bus sales and service company, and we have a lot of buses on the lot at a given time. All sales buses have stock cards in them. There are two types: internal stock and sold. All buses will have an internal stock card. Buses that customers have purchased will have a sold card. However, the sold cards don't always make it to the buses, and some buses get missed during the check-in process when they arrive from Tulsa. So what I am doing is getting the stock numbers off of the buses and putting them in an excel spreadsheet. What I then intend to do is grab the stock numbers and customer names off of our sold cards that have not been hung yet, and use the duplicates feature in excl to find which ones are on the lot and have sold but don't have the cards. The problem as you may imagine is that the duplicate feature looks at the text and counts it as data. So what I am looking to figure out is how I can make it ignore the customer names while still looking for duplicate stock numbers between columns. This is my first time using Excel, so please try to explain how I can do it in as simple a way as you can. Below will be an example of what stock numbers can look like with example customer names.


3245PNP21
3350PNP21 Smith Bus
B225PNG19D
B563PNL20T Johnson Trans

Hopefully these examples will help.

Thanks for any help you may be able to provide!


EDIT: I also intend to have the customer name in the column with the stock number. I'm trying to avoid doing a ton of work over the weekend.

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

Administrative Note:

Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[KClaps]

Sample workbook is attached. I'm in version 2204.

[AliGW]

Please read my instructions again - 2204 is a release number, not your version. Is it Office 365 that you have? Please update your profile accordingly as instructed earlier. Thanks.

Your sample workbook is inadequate: firstly, there are no duplicate codes that I can see, and secondly, you have not mocked up your expected results.

So, read my previous post again, please, and do everything that I have asked of you. Thanks.

[AliGW]

Maybe this is what you want:

=COUNTIF($A$2:$A$11,LEFT(A2,FIND(" ",A2)-1)&"*")

which can become a CF rule for A2 thus:

=COUNTIF($A$2:$A$11,LEFT(A2,FIND(" ",A2)-1)&"*")>1