Create unique "id" from codes with different cases

BiggusDoggus

Board Regular
Joined
Jul 7, 2014
Messages
88
Hi all

I have a large table including Salesforce Id's, which are supposed to be "unique", but unfortunately also use case to make them so.
The table uses data that includes a range of Salesforce Id's, from just one, to many rows with the same id.

I need a way to convert these id's into truly unique "id's", so I can apply formulas such as VLOOKUP, MAX IF etc etc etc, due to the fact that excel formulas are not case sensitive.
I know there's a way to do a VLOOKUP that's case sensitive, but I use the Salesforce Id's for other calculations that aren't VLOOKUP.

The Salesforce ID's are always 15 characters long: an example of two "unique" id's are:

0060I00000dseT3
0060I00000dsEt3

As you can see the case for the "e" and "t" are the only variations that make them "unique".

I have tried the following:

=CODE(MID(D2,1,1)) & CODE(MID(D2,2,1)) & CODE(MID(D2,3,1)) & CODE(MID(D2,4,1)) & CODE(MID(D2,5,1)) & CODE(MID(D2,6,1)) & CODE(MID(D2,7,1)) & CODE(MID(D2,8,1)) & CODE(MID(D2,9,1)) & CODE(MID(D2,10,1)) & CODE(MID(D2,11,1)) & CODE(MID(D2,12,1)) & CODE(MID(D2,13,1)) & CODE(MID(D2,14,1)) & CODE(MID(D2,15,1))

The output (using the above examples) is:

1595453362305.png



However, I tried a conditional format on the column to show duplicates, and according to it they are ALL duplicates!
And - I get the error box saying "Number stored as text", and the option to convert to Number. When I do:

1595453245197.png

Which is possibly why they are being considered as duplicates, and why none of my formulas are working as expected.


What's going wrong please?
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,147
Office Version
  1. 2016
Platform
  1. Windows

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
I'm a bit confused as to what you want!
Are you saying that these show as duplicates and you want to make them unique? If so, can you not simply append the row number!

=A1&row()

0060I00000dseT31
0060I00000dsEt32
 

BiggusDoggus

Board Regular
Joined
Jul 7, 2014
Messages
88
Hi BiggusDoggus,

You could append a unique identifier where necessary.

Book1
AB
1Salesforce IdNew Id
20060I00000dseT30060I00000dseT3-2
30060I00000dsEt30060I00000dsEt3-1
4DOGDOG-5
5dogdog-4
6UPPERUPPER-2
7lowerlower-2
8UPPERUPPER-1
9lowerlower-1
10DoGDoG-3
11dOGdOG-2
12dOgdOg-1
13ReallySameReallySame-2
14ReallySameReallySame-1
15ReallyUnique1ReallyUnique1
16ReallyUnique2ReallyUnique2
Sheet1
Cell Formulas
RangeFormula
B2:B16B2=IF(COUNTIF($A$2:$A$10002,A2)=1,A2,A2&"-"&COUNTIF(A2:$A$10002,A2))
Thanks - I literally just thought of that myself (along the same lines, at least)!

There is also a client id available - the chances of a "false" duplicate being created by the combination of the SF ID and the client id would be vanishingly remote, so have gone with that (using CONCATENATE).
 

Watch MrExcel Video

Forum statistics

Threads
1,118,186
Messages
5,570,747
Members
412,339
Latest member
sstackho
Top