Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-02-2011, 12:18 PM
djreyrey djreyrey is offline * Determine Capitalization Windows 7 * Determine Capitalization Office 2007
Novice
* Determine Capitalization
 
Join Date: Mar 2010
Posts: 21
djreyrey is on a distinguished road
Default * Determine Capitalization

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
Reply With Quote
  #2  
Old 05-02-2011, 01:48 PM
macropod's Avatar
macropod macropod is offline * Determine Capitalization Windows 7 32bit * Determine Capitalization Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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.

Code:
Sub Demo()
With ActiveSheet
  If UCase(.Range("A1").Value) = UCase(.Range("B1").Value) Then
    MsgBox "!"
  End If
End With
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Last edited by macropod; 05-03-2011 at 01:23 AM.
Reply With Quote
  #3  
Old 05-02-2011, 02:08 PM
djreyrey djreyrey is offline * Determine Capitalization Windows 7 * Determine Capitalization Office 2007
Novice
* Determine Capitalization
 
Join Date: Mar 2010
Posts: 21
djreyrey is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 05-02-2011, 02:33 PM
macropod's Avatar
macropod macropod is offline * Determine Capitalization Windows 7 32bit * Determine Capitalization Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by djreyrey View Post
I'm not sure what you mean by wrapping both expressions. Can you help me out with that with an example?
Didn't you look at the example I posted?
Quote:
Originally Posted by djreyrey View Post
Also, when applying this VBA code, will a message pop up for each match that is found?
That depend on your code, which you haven't posted.
Quote:
Originally Posted by djreyrey View Post
Is it possible to write some text in the next column with the words "Match" or something like that?
Yes, but (again) that depends on your code.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 05-02-2011, 09:31 PM
djreyrey djreyrey is offline * Determine Capitalization Windows 7 * Determine Capitalization Office 2007
Novice
* Determine Capitalization
 
Join Date: Mar 2010
Posts: 21
djreyrey is on a distinguished road
Default

Quote:
Didn't you look at the example I posted?
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".

Quote:
That depend on your code, which you haven't posted.
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.

Quote:
Yes, but (again) that depends on your code.
Same as above.
Reply With Quote
  #6  
Old 05-03-2011, 01:19 AM
macropod's Avatar
macropod macropod is offline * Determine Capitalization Windows 7 32bit * Determine Capitalization Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by djreyrey View Post
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".
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.
Quote:
Originally Posted by djreyrey View Post
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.
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]
Reply With Quote
  #7  
Old 05-03-2011, 10:45 AM
djreyrey djreyrey is offline * Determine Capitalization Windows 7 * Determine Capitalization Office 2007
Novice
* Determine Capitalization
 
Join Date: Mar 2010
Posts: 21
djreyrey is on a distinguished road
Default

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?
Reply With Quote
  #8  
Old 05-03-2011, 10:52 AM
djreyrey djreyrey is offline * Determine Capitalization Windows 7 * Determine Capitalization Office 2007
Novice
* Determine Capitalization
 
Join Date: Mar 2010
Posts: 21
djreyrey is on a distinguished road
Default

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!
Reply With Quote
  #9  
Old 05-03-2011, 02:03 PM
macropod's Avatar
macropod macropod is offline * Determine Capitalization Windows 7 32bit * Determine Capitalization Office 2000
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Hi Rey,

That will tell you which cells are in lower case, but not which lower-case cells are duplicates of upper-case cells:
Quote:
I need to remove the duplicate rows that have the ID letters lowercase and I need to keep the ID's with uppercase letters.
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]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to determine a user's mailbox size limit? enviroko Outlook 3 05-13-2010 07:02 AM
Macro to determine attached template zippyaus Word VBA 0 03-02-2010 02:05 AM
Anyway to determine time/date of text creation? pureride Word 1 01-05-2010 02:09 PM
* Determine Capitalization Determine age by using birthdate gdodson Excel 1 08-11-2006 09:27 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:07 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft