How do I group text from cells into categories

[bseeley]

I have cells with ethnic groups that I need to consolidate into broad ethnic
categories. For example, Cambodian, Chines, Filipino, etc into Asian/Pacific
Islander and White, White-Armenian, and White-European into White. This data
is in one column. I think this would be a long IF function, but can't set it
up.

Please help!

 

[Dave Peterson]

I would add a new worksheet (I'd name it: Ethnic Table) and add all the ethnic
groups in column A. The put their corresponding broader ethnic classes in
column B.

Then use this kind of formula to retrieve that group.

=vlookup(a2,'ethnic table'!a:b,2,false)

If you see any results of #n/a, that means that the item doesn't have a
corresponding entry in column A of the "ethnic table" worksheet.

Debra Dalgleish has lots of notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))
and
http://contextures.com/xlFunctions02.html#Trouble

 

[Pete_UK]

Build up a 2-column table somewhere in your sheet, like this:

Cambodian Asian/Pacific
Chinese Asian/Pacific
Filipino Asian/Pacific

You should list all your ethnic groups, together with the broader
classification. Suppose this occupies Y1 to Z25. Suppose also that
your ethnic data is in column E, and that your data starts on row 2.
Then in a helper column (eg H2) you could put this formula:

=VLOOKUP(E2,Y$1:Z$25,2,0)

and copy this down as far as you need to - this will give you your
broader classifications in column H.

Hope this helps.

Pete

 

[Shane Devenshire]

Hi,

Once you've got the larger categories you can use a pivot table to
summariize the data.