Create unique "id" from codes with different cases

[BiggusDoggus]

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:

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:

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?

 

[sandy666]

Use Power Query to be sure about case sensitive

 

[Toadstool]

Hi BiggusDoggus,

You could append a unique identifier where necessary.

Book1
	A	B
1	Salesforce Id	New Id
2	0060I00000dseT3	0060I00000dseT3-2
3	0060I00000dsEt3	0060I00000dsEt3-1
4	DOG	DOG-5
5	dog	dog-4
6	UPPER	UPPER-2
7	lower	lower-2
8	UPPER	UPPER-1
9	lower	lower-1
10	DoG	DoG-3
11	dOG	dOG-2
12	dOg	dOg-1
13	ReallySame	ReallySame-2
14	ReallySame	ReallySame-1
15	ReallyUnique1	ReallyUnique1
16	ReallyUnique2	ReallyUnique2
Sheet1

Cell Formulas
Range	Formula
B2:B16	B2	=IF(COUNTIF($A$2:$A$10002,A2)=1,A2,A2&"-"&COUNTIF(A2:$A$10002,A2))

 

[gaz_chops]

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]

Hi BiggusDoggus,

You could append a unique identifier where necessary.

Book1
	A	B
1	Salesforce Id	New Id
2	0060I00000dseT3	0060I00000dseT3-2
3	0060I00000dsEt3	0060I00000dsEt3-1
4	DOG	DOG-5
5	dog	dog-4
6	UPPER	UPPER-2
7	lower	lower-2
8	UPPER	UPPER-1
9	lower	lower-1
10	DoG	DoG-3
11	dOG	dOG-2
12	dOg	dOg-1
13	ReallySame	ReallySame-2
14	ReallySame	ReallySame-1
15	ReallyUnique1	ReallyUnique1
16	ReallyUnique2	ReallyUnique2
Sheet1

Cell Formulas
Range	Formula
B2:B16	B2	=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).