generate code

[apgmin]

In column A
I have the list of districts

Agar-Malwa
Alirajpur
Anuppur
Ashoknagar
Balaghat
Barwani
Betul
Bhind
Bhopal
Burhanpur
Chhatarpur
Chhindwara
Damoh
Datia
Dewas
Dhar
Dindori


In column B : I need a formula such that I can generate 2 unique characters to denote the districts

Similar to the 2 letters that denote the states in US like Alabama and Alaska both have the first 2 letters common but they are denoted by AL and AK

I need exactly something like this

 

[rlv01]

Similar to the 2 letters that denote the states in US like Alabama and Alaska both have the first 2 letters common but they are denoted by AL and AK

I need exactly something like this

There was no formula for the US states. People had to review them one by one and come to consensus over which two-letter abbreviation would be used. I suspect you man need to do that same.

 

[Joe4]

Yeah, and things could get a bit tricky, as you have to compare each one to all the other ones that have already been created.
And depending on how you do it, you could be using up other places two character abbreviation before you get there.

For example, let's say that you had some places whose first 3 letters are like this:
MAC...
MAI...
MIN...
MOP...

The first one would create "MA".
The second one cannot also use "MA". So if you look at the next letter, it would create "MI"
However, then when you get to the 3rd one, you cannot use "MI" because it is already used, even though its first two characters are different than everyone else's.

And what if you have a condition where all the letters are already used up?
Will you start to use letters NOT in the name, or maybe use numbers?

As you can see, there is a lot to consider, and no easy answers.
You would need to lay out the exact rules for us on how you want to create these codes, being sure to address the scenarios I mentioned above.
I am thinking that the solution will probably require VBA.

 

[Special-K99]

I'm assuming that's not a complete list.
I can only go by the data you've supplied, but using
- first character
- second character
- last character
would identify the district in question.

 

[apgmin]

There was no formula for the US states. People had to review them one by one and come to consensus over which two-letter abbreviation would be used. I suspect you man need to do that same.

Thanks for your advice, now is the age of AI, so expecting some help

 

[apgmin]

Yeah, and things could get a bit tricky, as you have to compare each one to all the other ones that have already been created.
And depending on how you do it, you could be using up other places two character abbreviation before you get there.

For example, let's say that you had some places whose first 3 letters are like this:
MAC...
MAI...
MIN...
MOP...

The first one would create "MA".
The second one cannot also use "MA". So if you look at the next letter, it would create "MI"
However, then when you get to the 3rd one, you cannot use "MI" because it is already used, even though its first two characters are different than everyone else's.

And what if you have a condition where all the letters are already used up?
Will you start to use letters NOT in the name, or maybe use numbers?

As you can see, there is a lot to consider, and no easy answers.
You would need to lay out the exact rules for us on how you want to create these codes, being sure to address the scenarios I mentioned above.
I am thinking that the solution will probably require VBA.

The scenario you envisage will be remote, as there will be only say 10 names begining with a particular character so the combinations cannot get exhausted

 

[apgmin]

CENTER CODE.xlsx
	F	G
1	MADHYA PRADESH	CODE
2	Agar-Malwa
3	Alirajpur
4	Anuppur
5	Ashoknagar
6	Balaghat
7	Barwani
8	Betul
9	Bhind
10	Bhopal
11	Burhanpur
12	Chhatarpur
13	Chhindwara
14	Damoh
15	Datia
16	Dewas
17	Dhar
18	Dindori
19	Guna
20	Gwalior
21	Harda
22	Indore
23	Jabalpur
24	Jhabua
25	Katni
26	Khandwa
27	Khargone
28	Maihar
29	Mandla
30	Mandsaur
31	MAUGANJ
32	Morena
33	Narmadapuram
34	Narsimhapur
35	Neemuch
36	Niwari
37	Pandhurna
38	Panna
39	Raisen
40	Rajgarh
41	Ratlam
42	Rewa
43	Sagar
44	Satna
45	Sehore
46	Seoni
47	Shahdol
48	Shajapur
49	Sheopur
50	Shivpuri
51	Sidhi
52	Singrauli
CENTER

G6 should come up with BA and G7 with BR as it is already taken earlier

 

[rlv01]

Thanks for your advice, now is the age of AI, so expecting some help

Then why are you not directing your request for help to an AI? There are only humans here.

 

[Joe4]

The scenario you envisage will be remote, as there will be only say 10 names begining with a particular character so the combinations cannot get exhausted

Can you describe the rule/logic you want to use?
In your list alone, you have 4 options that start with MA, and a a few that start with MAN.

Quite frankly, I do not think it is worth the effort trying to come up with some sort of automated VBA code to tried to do this for you, when there are so many variations.
I would simply take the two left-most characters, and then use a Conditional Formatting rule to highlight all the duplicates (ex. MA), and then change those manually.

If you truly want something more automated, and it doesn't really matter what the codes are, how about sorting your list alphabetically, and just take the first letter, then make the second go in order, i.e. MA, then MB, then MC, etc. As long as you don't have 26 different options starting with the same letter, that should work. If you do have more than 26 options starting with the same letter, then the code really aren't going to resemble the names at all, and you might as well just start with AA, AB, AC, etc.