Categorize from list base on list

[freqzz]

Hi everyone,

Sorry for the title, a bit confusion. Not sure if I cant articulate this well enough.

I would like to categorize a set of information based on a list. Let me show you what i mean.

This is the information I have.

City

HK
Hong Kong
New York
Jersey
New Jersey
Manila
Makati

If I group that together it will come back to 7 group.

Would like to simplify base on a list (or dictionary that can be add on) like this

List	Group
HK	HK
hon kon	HK
heh kee	HK
Us	US
USA	US
america	US
NY	US

<tbody>
</tbody>

So what im trying to do is for each city, look at the list and group them accordingly per group.

Is that possible?

Regards,
Freqzz

 

[dgyang]

In my opinion, you need first have a mapping table for the cities and countries, like the one you show above.

Then use "VLOOKUP" function to identify the "Group" for each city, based on the mapping table.

 

[freqzz]

Yes, I was thinking about vlookup but the trick is, if the city contains other unrelated comments. Maybe I should have make it clear.. say if i have a field contain :

City
Trip to HK
Business ticket to hong kong
return from NY
New York visit

How do i manage those?

 

[dgyang]

One way I can think of is use macro to loop each city in your mapping table, search if the text contains the city name. If yes, identify the corresponding group in your mapping. If not, loop to the next city name in your mapping table.

You can use "instr" to check if the city name is within the text. If the returned value is larger than 0, the city name is in the text.

 

[freqzz]

Thanks, that would be a different thread I guess.

Thanks again.

 

[freqzz]

Ok i found the solution but just one issue. If you open the spreadsheet, how do I change the formula to reflect supposed result which is in column C (which looksup to colum E)

A B C D E

Purpose	Formula Result	Supposed Result		List	Group
HK trip	HK	Hong Kong		HK	Hong Kong
Operation impovement hong kong	Hong Kong	Hong Kong		Hong Kong	Hong Kong
Business related ny	NY	New York		NY	New York
N York	N York	New York		N York	New York

<tbody>
</tbody>

The formula i used is :

=INDEX($E$2:$E$6,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($E$2:$E$6,A2)),),0))

Please help.

Thanks

 

[freqzz]

Bump?

 

[dgyang]

Try this:

=INDEX($E$2:$E$6,MATCH(TRUE,ISNUMBER(SEARCH($D$2:$D$6,A2)),0))

Use Ctrl+Shift+Enter to change it to array formula. I guess it's what you want?