catagorizing words into there related families

[Bob N.]

Hi there all,
What I am tring to do is eventually create a chart that
will show how many words in a colum are in there related
family. What I mean by this is lets say that I have these
values...

cell b1 = "apples"
cell b2 = "carrots"
cell b3 = "onions"
cell b4 = "pinapples"
cell b5 = "oranges"
cell b6 = "cellery"

obvisually by looking at these
values, "apples", "pinapples", and "oranges" are all in
the fruit family, and "carrots", "onions", "cellery" are
all in the vegetables family. Though this is just an
example whereas it could be catagorizing types of cars
and what size engine they have etc.

What I want excell to do is first catagories these
families automatically so that I can use it as a template
that I can choose whetever I want to catagorize. Then
after excell catagorizes the words to there proper
family, I want excell to count how many words excell can
find in that piticular catagory family, which is in this
example there are 3 fruits, and there 3 vegetables, and
then place these amounts in a chart. Is this possible? If
so how could I go about that?

 

[Harlan Grove]

What I am tring to do is eventually create a chart that
will show how many words in a colum are in there related
family. What I mean by this is lets say that I have these
values...

cell b1 = "apples"
cell b2 = "carrots"
cell b3 = "onions"
cell b4 = "pinapples"
cell b5 = "oranges"
cell b6 = "cellery"

obvisually by looking at these
values, "apples", "pinapples", and "oranges" are all in
the fruit family, and "carrots", "onions", "cellery" are
all in the vegetables family. Though this is just an
example whereas it could be catagorizing types of cars
and what size engine they have etc.

You know that, I know that, most humans reading this newsgroup know that.
Excel, on the other hand, doesn't know that. Indeed, Excel *knows* virtually
nothing other than how to interpret and calculate formulas.

What I want excell to do is first catagories these
families automatically so that I can use it as a template
that I can choose whetever I want to catagorize. . . .

The only way Excel could do this - dividing ranges of words into
categories - is if *you* create a master table of item identifiers and their
respective categories. Something like

apples fruit
carrots vegetable
celery vegetable
onions vegetable
oranges fruit
pineapples fruit

Note that this table is sorted in ascending order in the first column.

. . . Then
after excell catagorizes the words to there proper
family, I want excell to count how many words excell can
find in that piticular catagory family, which is in this
example there are 3 fruits, and there 3 vegetables, and
then place these amounts in a chart. Is this possible? If
so how could I go about that?

Given a list of items like your sample B1:B6 range and a table of item to
category associations like I show above (and to which I'll refer as ICTBL),
you'd count the number of fruits (including duplicates) using formulas like

=SUMPRODUCT(--(LOOKUP($B$1:$B$6,ICTBL)="fruit"))

and the number of distinct fruit (ignoring duplicates) using formulas like

=COUNT(1/FREQUENCY((LOOKUP($B$1:$B$6,ICTBL)="fruit")
*MATCH($B$1:$B$6,INDEX(ICTBL,0,1)),ROW(ICTBL)-CELL("Row",ICTBL)))-1

 

[Gord Dibben]

Bob

Excel knows diddly about fruits and vegetables.

You will have to let Excel know which is which by some method.

A helper column with "fruit" or "vegetable" at the appropriate corresponding
cells.

Column A has the list of names.
Column B has corresponding list of categories(families).

You could then Autofilter or Sort by name or family and use COUNT or COUNTIF
functions.

Gord Dibben Excel MVP

 

[Bob N.]

Master Table? Meaning like there is a Master Slide in
PowerPoint? And how do you refer to a table in Excell?

I ask this because when I typed in this formula...
=SUMPRODUCT(--(LOOKUP($B$1:$B$6,ICTBL)="fruit"))

Excell gives me a #value error and when I evaluated this
formula the error refers to ICTBL.

 

[Frank Kabel]

Hi Bob
Harlan used the phrase 'Master Table' to name the data range which
contains the 'master' categorization. It is nothing similar to the
'master slide' in PPT.

For your second question: As Harlan said 'ICTBL' refers to this data
range of categories. So either define a name ('Insert - Name - Define')
ICTBL which refers to this data range or simply replace ICTBL with the
specific cell reference of this range (e.g. 'sheet1'!A1:B10)