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

[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]List[/TD]
[TD="width: 64"]Group[/TD]
[/TR]
[TR]
[TD]HK[/TD]
[TD]HK[/TD]
[/TR]
[TR]
[TD]hon kon[/TD]
[TD]HK[/TD]
[/TR]
[TR]
[TD]heh kee[/TD]
[TD]HK[/TD]
[/TR]
[TR]
[TD]Us[/TD]
[TD]US[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]US[/TD]
[/TR]
[TR]
[TD]america[/TD]
[TD]US[/TD]
[/TR]
[TR]
[TD]NY[/TD]
[TD]US[/TD]
[/TR]
</tbody>[/TABLE]


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
[TABLE="width: 732"]
<tbody>[TR]
[TD="align: center"]Purpose[/TD]
[TD="align: center"]Formula Result[/TD]
[TD="align: center"]Supposed Result[/TD]
[TD="align: center"][/TD]
[TD="align: center"]List[/TD]
[TD="align: center"]Group[/TD]
[/TR]
[TR]
[TD]HK trip[/TD]
[TD]HK[/TD]
[TD]Hong Kong[/TD]
[TD][/TD]
[TD]HK[/TD]
[TD]Hong Kong[/TD]
[/TR]
[TR]
[TD]Operation impovement hong kong[/TD]
[TD]Hong Kong[/TD]
[TD]Hong Kong[/TD]
[TD][/TD]
[TD]Hong Kong[/TD]
[TD]Hong Kong[/TD]
[/TR]
[TR]
[TD]Business related ny[/TD]
[TD]NY[/TD]
[TD]New York[/TD]
[TD][/TD]
[TD]NY[/TD]
[TD]New York[/TD]
[/TR]
[TR]
[TD]N York[/TD]
[TD]N York[/TD]
[TD]New York[/TD]
[TD][/TD]
[TD]N York[/TD]
[TD]New York[/TD]
[/TR]
</tbody>[/TABLE]

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?