Is there a bug in Excel RemoveDuplicates

[hicksi]

I have a list of words. And now I'm trying to ensure that they are all unique.

If I take a copy of that list and RemoveDuplicates, then that removes duplicates, IRREGARDLESS of case.
I then place a COUNTIF against that list to find the duplicate which has a count of 2.
I then remove all count-1 entries, and do a VLOOKUP from the full list of the words with Count-2 (or more).
THAT process usually gives me a proper list that I can peruse and ensure that the 'correct' version of the word is removed (based on the perused case).

The ISSUE that arose is when the word Æterna is the duplicate.
VLOOKUP only finds Æterna, not Aeterna.

Ie, REMOVEDUPLICATES sees them as the same word, COUNTIF sees them as the same word, but VLOOKUP does not.
Is this intended, or an irregularity in the way 'case' is handled. And is there a workaround (as there's probably other instances that fail)

 

[hicksi]

Has anyone looked into this with Microsoft, or am I the only person to have ever encountered this problem?

 

[Alex Blakenburg]

Interesting. What version of Excel are you running. Please update your profile accordingly.
I am a bit surprised that any functions recognise it as a match (they even come up as being different lengths) but as you pointed out most do.
For context I am running MS 365

The ones that do work include:
• =
• CountIf(s) etc
• Duplicate Functions
• The newer functions: XLookup, XMatch, Filter (the latter probably because "=" works)

What does not work:
• VLookup
• Match
With these functions being replaced with the newer equivalents, it is unlikely that you will get MS to look into this.

 

[hicksi]

Interesting. What version of Excel are you running. Please update your profile accordingly.
I am a bit surprised that any functions recognise it as a match (they even come up as being different lengths) but as you pointed out most do.
For context I am running MS 365

The ones that do work include:
• =
• CountIf(s) etc
• Duplicate Functions
• The newer functions: XLookup, XMatch, Filter (the latter probably because "=" works)

What does not work:
• VLookup
• Match
With these functions being replaced with the newer equivalents, it is unlikely that you will get MS to look into this.

I'm happy. I came back on here for another (somewhat) obscure problem where a startup routine sometimes made a form properly active, and other times left the cursor somewhere in limbo.
That was resolved by a DoEvents loop which created a delay long enough to activate.

Frankly, this was the last thing I asked, and I was surprised there wasn't an answer.
I'll convert my VLookup to XLookup now in all my processing.

FYI
Microsoft® Excel® 2021 MSO (Version 2410 Build 16.0.18129.20158) 64-bit
The single-purchase version.

 

[Peter_SSs]

FYI
Microsoft® Excel® 2021

Please update you your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[hicksi]

Please update you your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Done. Not the friendliest system to find where you need to go. But I found it and updated.

 

[Peter_SSs]

Done.

Thank you.