Salesforce.com record ID’s are case sensitive so Comparing data sets using Microsoft Excel can be tricky as you will quickly learn Microsoft Excel does not handle comparing and looking up values that require case sensitivity.
Microsoft Solution
You will find a couple of options fairly quickly with this Microsoft Support page “How to Perform a Case-Sensitive Lookup” and think eureka! I have got it now! Not so fast, this function will puke the first time it compares two ID’s with varying case that do not match. You will get “No Match Found” for each record that has two ID’s with different cases. This quickly becomes a less than ideal solution specially with large data sets.
Google Solution
Another option is to transform the case sensitive 15 character ID to a unique 18 character case insensitive ID and the use vlookup with impunity. Several examples of this process can be found on the web but one great example can be found here: Judi Sohn: Case-sensitive vlookups for Salesforce in Excel 2003. Judi does a great job summarizing everything I just reviewed as well as a straightforward process for creating the 18 character ID.
Ultrageek Solution
What i would recommend for all objects is creating an “Autonumber” field for a couple of reasons.
- Unique – The number is unique and generated every time a record is created. It can easily be used for vlookups.
- Comparison – Easy to compare, troubleshoot, and track.
- Reference – Can be used and referred to as an Account, Contact, or Asset number. Make it easy to refer and reference.
- Comfort – You can rest assured that you do not need a PhD. or a computer science degree to work with these records.
- Training – Allows for other non-IT groups and users do their own reporting and data analysis without pulling in IT/SFDC reporting resources
And the best part is you can add it any time and with the “Generate Auto Number for existing records” option you will not need to data load any values for existing records.
One tip, the display format example shows using a prefix. You do not have to use one, you can just use bracket number length bracket so that it just generates the number. I’ve attached a screenshot for reference.
