- 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)) - 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"})

## No comments:

## Post a Comment