I have received 8 SalesForce.com certifications and have been using SalesForce for nearly 9 years and have learned a lot about the system. I have used it to migrate companies from past on premise systems to the cloud. I have also handled all data migrations and even moved one organization from Unlimited Edition to Enterprise Edition through setting up a new system and migrating all data. I believe in the vision in SalesForce and want to help others leverage its power.

Monday, July 27, 2015

Two Useful Excel Formulas You Will Need

With SalesForce, ideally you can do everything up in the cloud. However, there are times you have to prepare data for upload into SalesForce. When that happens, often it is fairly easy to prepare data for import. But if you are doing an update (on a record), because of the way SalesForce structures their record IDs, there are two major gotchas! They are:

The Record IDs are case sensitive. So, 00Od0000004PuiV and 00Od0000004Puiv would take you to different records

The Record IDs are sometimes 15 characters and sometimes 18 characters (which means when you update a record, you are updating the wrong record if you are not using the right ID)

So, as a result of this, there are some tricks/formulas you can use in Excel to make things easier. Here they are:

To lookup IDs, case sensitively, you can use this formula:

{=INDEX(NEW!$C$2:$C$7050,MATCH(TRUE,EXACT(NEW!$A$2:$A$7050,A2),0))

For this formula, cells $C$2:$C$7050 would be where you want the results to be put in Excel. Cells $A$2:$A$7050 would be where your current IDs start. Then ensure you put it in as an Array (when editing the formula press Shift and Command and Enter at the same time)

To look at a 15 character ID and convert it to an 18 character ID, you can use this formula: =A2&LOOKUP(IF(AND(CODE(RIGHT(LEFT($A2,5),1))>=65,CODE(RIGHT(LEFT($A2,5),1))<=90),1,0) & IF(AND(CODE(RIGHT(LEFT($A2,4),1))>=65,CODE(RIGHT(LEFT($A2,4),1))<=90),1,0) & IF(AND(CODE(RIGHT(LEFT($A2,3),1))>=65,CODE(RIGHT(LEFT($A2,3),1))<=90),1,0)& IF(AND(CODE(RIGHT(LEFT($A2,2),1))>=65,CODE(RIGHT(LEFT($A2,2),1))<=90),1,0)& IF(AND(CODE(LEFT($A2,1))>=65,CODE(LEFT($A2,1))<=90),1,0),{"00000","00001","00010","00011","00100","00101","00110","00111","01000","01001","01010","01011","01100","01101","01110","01111","10000","10001","10010","10011","10100","10101","10110","10111","11000","11001","11010","11011","11100","11101","11110","11111"},{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","0","1","2","3","4","5"}) & LOOKUP(IF(AND(CODE(RIGHT(LEFT($A2,10),1))>=65,CODE(RIGHT(LEFT($A2,10),1))<=90),1,0) & IF(AND(CODE(RIGHT(LEFT($A2,9),1))>=65,CODE(RIGHT(LEFT($A2,9),1))<=90),1,0) & IF(AND(CODE(RIGHT(LEFT($A2,8),1))>=65,CODE(RIGHT(LEFT($A2,8),1))<=90),1,0)& IF(AND(CODE(RIGHT(LEFT($A2,7),1))>=65,CODE(RIGHT(LEFT($A2,7),1))<=90),1,0)& IF(AND(CODE(RIGHT(LEFT($A2,6),1))>=65,CODE(RIGHT(LEFT($A2,6),1))<=90),1,0),{"00000","00001","00010","00011","00100","00101","00110","00111","01000","01001","01010","01011","01100","01101","01110","01111","10000","10001","10010","10011","10100","10101","10110","10111","11000","11001","11010","11011","11100","11101","11110","11111"},{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","0","1","2","3","4","5"})&LOOKUP(IF(AND(CODE(RIGHT(LEFT($A2,15),1))>=65,CODE(RIGHT(LEFT($A2,15),1))<=90),1,0) & IF(AND(CODE(RIGHT(LEFT($A2,14),1))>=65,CODE(RIGHT(LEFT($A2,14),1))<=90),1,0) & IF(AND(CODE(RIGHT(LEFT($A2,13),1))>=65,CODE(RIGHT(LEFT($A2,13),1))<=90),1,0)& IF(AND(CODE(RIGHT(LEFT($A2,12),1))>=65,CODE(RIGHT(LEFT($A2,12),1))<=90),1,0)& IF(AND(CODE(RIGHT(LEFT($A2,11),1))>=65,CODE(RIGHT(LEFT($A2,11),1))<=90),1,0),{"00000","00001","00010","00011","00100","00101","00110","00111","01000","01001","01010","01011","01100","01101","01110","01111","10000","10001","10010","10011","10100","10101","10110","10111","11000","11001","11010","11011","11100","11101","11110","11111"},{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","0","1","2","3","4","5"})