Folks,
I have multiple sheets containing personal info which i'm maintaining. Of interest is the data in
I need a formula to compare only yymmdd-<B><font color=red>??</font></B>-xxxx with data (columns B through F) in another file as shown below:
The output of the comparison is going into a new column in the 1st sheet.
An example: 740910-<b><font color=red>03</font></b>-5071 should give me <b><font color=red>kelantan</font></b> while 800603-<b><font color=red>08</font></b>-5388 would give <b><font color=red>perak</font></b>
Any idea?
I have multiple sheets containing personal info which i'm maintaining. Of interest is the data in
Excel Workbook | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Bil | Nama | No. K/P | Tempat | ||
2 | 1 | Ahmad Muttaqin Rabbani bin Abdullah | 740910-03-5071 | Penolong Pendakwa Raya Pejabat Pendakwaan Cawangan Wilayah Persekutuan Kuala Lumpur (Mahkamah Sesyen Kuala Lumpur) Gred L41 (Kontrak) | ||
3 | 2 | Suhaima binti Mohd Noor | 800210-03-5128 | Penolong Pendakwa Raya Pejabat Pendakwaan Cawangan Wilayah Persekutuan Kuala Lumpur (Mahkamah Sesyen Kuala Lumpur) Gred L41 (Kontrak) | ||
4 | 3 | Hidayatul Syuhada binti Shamsudin | 820620-14-5870 | Penolong Pendakwa Raya Pejabat Pendakwaan Cawangan Wilayah Persekutuan Kuala Lumpur (Mahkamah Majistret Kuala Lumpur) Gred L41 (Kontrak) | ||
5 | 4 | Jamaliah binti Abdul Manap | 800603-08-5388 | Penolong Pendakwa Raya Pejabat Pendakwaan Cawangan Wilayah Persekutuan Kuala Lumpur (Mahkamah Majistret Kuala Lumpur) Gred L41 (Kontrak) | ||
column C as in sample below. They are personal identification numbers.
wp k_l |
I need a formula to compare only yymmdd-<B><font color=red>??</font></B>-xxxx with data (columns B through F) in another file as shown below:
Excel Workbook | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | Johor | 01 | 21 | 22 | 23 | 24 | ||
2 | Kedah | 02 | 25 | 26 | 27 | * | ||
3 | Kelantan | 03 | 28 | 29 | * | * | ||
4 | Melaka | 04 | 30 | * | * | * | ||
5 | Negeri Sembilan | 05 | 31 | 59 | * | * | ||
6 | Pahang | 06 | 32 | 33 | * | * | ||
7 | Pulau Pinang | 07 | 34 | 35 | * | * | ||
8 | Perak | 08 | 37 | 38 | 39 | * | ||
9 | Perlis | 09 | 40 | * | * | * | ||
10 | Selangor | 10 | 41 | 42 | 43 | 44 | ||
11 | Terengganu | 11 | 45 | 46 | * | * | ||
12 | Sabah | 12 | 47 | 48 | 49 | * | ||
13 | Sarawak | 13 | 50 | 51 | 52 | 53 | ||
14 | WP Kuala Lumpur | 14 | 54 | 55 | 56 | 57 | ||
15 | WP Labuan | 15 | 58 | * | * | * | ||
16 | WP Putrajaya | 16 | * | * | * | * | ||
State Codes |
The output of the comparison is going into a new column in the 1st sheet.
An example: 740910-<b><font color=red>03</font></b>-5071 should give me <b><font color=red>kelantan</font></b> while 800603-<b><font color=red>08</font></b>-5388 would give <b><font color=red>perak</font></b>
Any idea?