Assign numeric code to data table

[sly_sud]

I have a table with information in the following manner.

Continent	Country	City
Asia	China	Beijing
Asia	China	Tianjin
Asia	India	Bengaluru
Asia	India	Mumbai
Asia	Thailand	Bangkok
Europe	France	Lyon
Europe	France	Paris
Europe	Germany	Berlin
Europe	Germany	Munich
Europe	UK	London
Europe	UK	Manchester
North America	Canada	Montreal
North America	Canada	Toronto
North America	Mexico	Guadalajara
North America	Mexico	Mexico City
North America	USA	New York
North America	USA	Washington

<colgroup><col><col><col></colgroup><tbody>
</tbody>

I want to assign numeric code to the cities in this manner.

Continent	Country	City	Code
Asia	China	Beijing	01-01-001
Asia	China	Tianjin	01-01-002
Asia	India	Bengaluru	01-02-001
Asia	India	Mumbai	01-02-002
Asia	Thailand	Bangkok	01-03-001
Europe	France	Lyon	02-01-001
Europe	France	Paris	02-01-002
Europe	Germany	Berlin	02-02-001
Europe	Germany	Munich	02-02-002
Europe	UK	London	02-03-001
Europe	UK	Manchester	02-03-002
North America	Canada	Montreal	03-01-001
North America	Canada	Toronto	03-01-002
North America	Mexico	Guadalajara	03-02-001
North America	Mexico	Mexico City	03-02-002
North America	USA	New York	03-03-001
North America	USA	Washington	03-03-002

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

What formula should I use to create this number code. Please help.

Thanks in advance.

 

[DILIPandey]

Okay.. with your first table in column A,B,C, type 1 in D2, E2 and F2, now apply below formula in

D3:-
=IF(A3=A2,D2,D2+1)

E3:-
=IF(A3<>A2,1,IF(AND(A3=A2,B3=B2),E2,E2+1))

F3:-
=COUNTIF($B$2:B3,B3)

and drag down.

Now, if you wish you can concatenate these three codes to appear in a single column

Regards,
DILIPandey

 

[Special-K99]

in D1 put
01-01-001

in D2 put
=TEXT(LEFT(E1,2)+IF(A2=A1,0,1),"00-")&TEXT(IF(A1<>A2,1,MID(E1,4,2)+IF(B2<>B1,1,0)),"00-")&TEXT(IF(OR(A1<>A2,B1<>B2),1,RIGHT(E1,3)+1),"000")
and copy down the column

 

[bosco_yip]

In D2, formula copy down :

=TEXT(SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2)),"00-")&TEXT(SUMPRODUCT((A$2:A2=A2)/COUNTIFS(A$2:A2,A$2:A2,B$2:B2,B$2:B2)),"00-")&TEXT(COUNTIF(B$2:B2,B$2:B2),"000")

 

[sly_sud]

Hi

Thank you for the help. Really appreciate it. But the formula you gave isn't working. This is what I am getting as a response

Continent	Country	City
Asia	China	Beijing	02-01-001
Asia	China	Tianjin	01-01-002
Asia	India	Bengaluru	01-02-001
Asia	India	Mumbai	01-01-002
Asia	Thailand	Bangkok	01-02-001
Europe	France	Lyon	02-01-001
Europe	France	Paris	01-01-002
Europe	Germany	Berlin	01-02-001
Europe	Germany	Munich	01-01-002
Europe	UK	London	01-02-001
Europe	UK	Manchester	01-01-002
North America	Canada	Montreal	02-01-001
North America	Canada	Toronto	01-01-002
North America	Mexico	Guadalajara	01-02-001
North America	Mexico	Mexico City	01-01-002
North America	USA	New York	01-02-001
North America	USA	Washington	01-01-002

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

I used the formula in this manner =TEXT(LEFT($E$1,2)+IF(A2=A1,0,1),"00-")&TEXT(IF(A1<>A2,1,MID($E$1,4,2)+IF(B2<>B1,1,0)),"00-")&TEXT(IF(OR(A1<>A2,B1<>B2),1,RIGHT($E$1,3)+1),"000")

Thanks again.

 

[bosco_yip]

in D1 put
01-01-001

in D2 put
=TEXT(LEFT(E1,2)+IF(A2=A1,0,1),"00-")&TEXT(IF(A1<>A2,1,MID(E1,4,2)+IF(B2<>B1,1,0)),"00-")&TEXT(IF(OR(A1<>A2,B1<>B2),1,RIGHT(E1,3)+1),"000")
and copy down the column

Modify Special-K99's formula in D1 to meet with the OP desired Output layout

(I think D1 is a typo, and should read as 00-01-001)

1] In D1, enter : 00-01-001

then, >> D1 >> Custom Cell Formatting, enter : 0;0;0;"CODE"

2] D2 copydown formula same as Special-K99's

 

[sly_sud]

It worked like a charm. Thank you very much bosco

 

[bosco_yip]

It worked like a charm. Thank you very much bosco

Glad to help

 

[sly_sud]

Hi

I am coming back with a similar query again. Turns out the data list I had was not the final and new additions will keep happening to it. So I need a numeric code as previously, only this time, that data list is unsorted and the list will keep increasing.

The data looks like this, but it won't be sorted alphabetically. And the code at the end is what I need.

Continent Country City Code
Asia China Beijing 01-01-001
Asia China Tianjin 01-01-002
Asia India Bengaluru 01-02-001
Asia India Mumbai 01-02-002
Asia Thailand Bangkok 01-03-001
Europe France Lyon 02-01-001
Europe France Paris 02-01-002
Europe Germany Berlin 02-02-001
Europe Germany Munich 02-02-002
Europe UK London 02-03-001
Europe UK Manchester 02-03-002
North America Canada Montreal 03-01-001
North America Canada Toronto 03-01-002
North America Mexico Guadalajara 03-02-001
North America Mexico Mexico City 03-02-002
North America USA New York 03-03-001
North America USA Washington 03-03-002


Thanks in advance.