duplicates check

[scouse13]

hi
I know excel only reads to a maximum of 15 numbers, but does anyone have any code, that can check for duplicates on 18 digit numbers, all the code I have tried so far has not worked, the code would need to check a column, maybe 500 rows long, where all the entries are 18 numbers long,

one idea I had was maybe, can excel, disregard the first 3 numbers of the entry and then check for duplicates.
each entry has to be 18 numbers long.

thanks

scouse13

[CAntosh]

Do you calculate with them? If not, then you can format the column as text. Excel is fine with 18 character text entries.

[scouse13]

Do you calculate with them? If not, then you can format the column as text. Excel is fine with 18 character text entries.

the cells are formatted as text, the first 14 digits are normally the same only the last 4 change, but all 18 digits need to be entered

scouyse13

[CAntosh]

Can you post a sample of your workbook with all sensitive data removed/altered?

[scouse13]

Can you post a sample of your workbook with all sensitive data removed/altered?

workbook attached thanks cantosh, all help appreciated

scouse13

[CAntosh]

I'm afraid that I'm either not seeing or not understanding the problem you're running into. When I use Excel's built-in 'Remove Duplicates' button, and check the box to look for duplicates only in column F/Carton#, it removes only Row 9/Rte. 5, as it should, and all but one of the blank entries. The blank issue can be remedied by selecting only the 7 rows with carton numbers. Can you give me more details on the nature of the problem? Are you trying to create a custom duplicate removal procedure to use instead of the built-in version?

[scouse13]

cantosh

I am using excel 2203, in this version remove duplicates is not available, that's why I was using vba, I don't want the items removed just highlighted so the user can decide to reove or not or change the carton number, I did put some notes on a separate sheet to try to explain what I need

[CAntosh]

Ah! Got it. Would you prefer a conditional formatting rule?

[CAntosh]

Here's a VBA solution that should work. It's currently set to highlight dupes in red. I think a conditional formatting rule would be more effective, since it would update automatically, but you were right about the headaches it creates as it struggles with the length of the string.

Please Login or Register  to view this content.

[scouse13]

thanks cantosh
will try your code first, I have tried the conditional formatting route, but that did not seem to work either, will get back to you once I have tried the code

thanks

scouse13

[CAntosh]

Applying this conditional formatting rule to your range in column F will highlight the first of the two duplicate values. If there are 3+ dupes, then it will highlight all but the last one.

=MATCH($F5,$F6:$F100,0)>0

It's set to search for a duplicate through F100, but adjust that to meet/exceed your number of rows.

[scouse13]

thanks cantosh
the vba still had the same problems as before, tried the conditional formatting, that works to a degree, can it be set to highlight the 2 rows of numbers that are the same, as the same number is unlikely to be entered more than twice

scouse13

[CAntosh]

The VBA works reliably for me? I've also come with a conditional formatting rule that, when applied to F5:F20 will work almost perfectly. It should only be fooled by an account number that has the same first 15 digits as one entry and the same last 15 digits as a different entry.

=AND(COUNTIF($F$5:$F$20,"*"&RIGHT($F5,15))>1,COUNTIF($F$5:$F$20, LEFT($F5,15)&"*")>1)

[scouse13]

thanks for your help cantosh
will try the new formatting rule in the morning, getting late here,
will let u know how I get on

scouse13