Hello all,



Thank you for stopping by. I have a spreadsheet with many rows. Each row has an ID made up of numbers and letters. For some reason, when I exported the data from our database some records were duplicated. However, the duplicated records have the letters in the ID lowercase. I need to remove the duplicate rows that have the ID letters lowercase and I need to keep the ID's with uppercase letters.

Is there a formula or VBA that will do this for me?

Example 1:
123RT456 - (keep)
123rt456 - (remove)

Example 2:
9123847JH2351234H - (keep)
9123847jh2351234h - (remove)

I really appreciate your help!

Rey

Hi Rey,

When doing the VBA comparison, wrap both expressions in the UCase function. This will force the comparison to be done on the basis of the capitalized versions of the strings.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thanks Macropod for your reply!

I'm not sure what you mean by wrapping both expressions. Can you help me out with that with an example?

Also, when applying this VBA code, will a message pop up for each match that is found? That wouldn't be the best option because there's hundreds of rows where the ID's match.

Is it possible to write some text in the next column with the words "Match" or something like that?

Thanks again!

Rey

Didn't you look at the example I posted?
That depend on your code, which you haven't posted.
Yes, but (again) that depends on your code.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Yes, I did look at your code which is why I'm asking you this question. I'm a newbie! I don't understand what you mean by "wrapping both expressions".

I have not posted any code because I don't have any. Which is another reason why I'm here. I don't know how to proceed.

Same as above.

Given the nature of the code you've posted in the past, it was not apparent that you don't know how to code. As for what I mean by "wrapping both expressions" with the Ucase function, a quick read of the code reveals:
If UCase(.Range("A1").Value) = UCase(.Range("B1").Value) Then
In this example, the Ucase function encompasses the other expression on each side of the '=' symbol.
It would have been helpful had you said that's the kind of help you need.

What you're asking can't be done via formulae, since formulae can't delete rows. So, unless you want to use Excel's 'UPPER' worksheet function to compare the values in your ranges, then manually delete the duplicate rows, you'll need vba. And, before anyone can provide a vba solution, you'll need to supply details of the ranges to be compared and, where a match is found, which of those ranges is to be deleted and which is to be flagged.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

At this point, I don't need to remove rows. I just want Excel to flag which rows have the ID numbers in lowercase letters.

I can manually remove them after they're flagged. Is your example the best and/or easiest way to do this?

I just found exactly what I was looking for. Here's the formula:

=IF(EXACT(B4,LOWER(B4)),"Lower Case","")

All I needed to know was which ID's in Column B where lower case.

Thanks for your efforts!

Hi Rey,

That will tell you which cells are in lower case, but not which lower-case cells are duplicates of upper-case cells:
You still haven't said what range the data spans. As it now appears to span part or all of Column B, you could use a formula like:
=TRIM(IF(COUNTIF(B$1:B1,B1)> 1,"Duplicate","")&" "&IF(EXACT(B1,LOWER(B1)),"Lower Case",""))
if you put the above formula in, say, D1 and copy it down as far as needed, it will report both duplicates (regardless of case) and lower-case forms (regardless of duplication).

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]