Counting Unique Long Numbers (>15 digits)

I'm working a file with long tracking numbers, some of which are duplicated. I need to note whether each individual tracking number is a duplicate or not in a "Unique" column. This column should should list a "1" value for new tracking numbers and a "0" for repeated tracking numbers such that every tracking number lists a "1" when first listed. I've usually used IF(COUNTIF for these formulas, but COUNTIF is limited to only counting the first 15 digits as significant - many of my tracking numbers are longer. This means my file, using IF(COUNTIF says "328498283148983448323" and "328498283148983448324" are the same tracking numbers, even though they are one digit apart - if the "..323" record appears first, it lists a "1", while the "..324" record lists a "0".

Unfortunately, I can't just delete file duplicates or use a catchall SUMPRODUCT to say "there are X# unique records in the file" - I need to specify row-by-row. Can someone please help with this? It's driving my team crazy. Sample file attached.

Cell B2 formula , drag down

Formula:

Please Login or Register  to view this content.

@eNinjaInTraining You're Welcome. Glad to help . Thank You for the feedback


If you finally get a solution please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".
- Please click on the *Add Reputation button at the bottom left. you can thank those who have helped

wk9128, could you help explain what the &"*" adds to the formula? I'm not having luck finding online. Is this wildcard just telling Excel "consider everything in the cell, not just the first 15 characters"?

Found link online (below) explaining &"*" - thanks, everyone.

https://www.excel-university.com/vlo...partial-match/

ans. Post#8

This is mainly because Excel can only judge the first 15 digits. If the number exceeds 15 digits, the following digits are directly ignored. Therefore, for bank account numbers, as long as the first 15 digits are the same, the countif() function will consider them to be in the statistics. identical. To solve this problem, it is necessary to cleverly set the statistical conditions of counif(), and connect the wildcard character * after the original condition "A2", and that's it.

Thank you, wk9128!