Multiple Criteria to Classify groups

[Karoline]

HI! I am a dummy beginner but very interested in improve my skills. I have
been 4 hours trying to understand the logic on multiple criteria formulas and
I am afraid I am almost going to give up... Someone around could PLEASE help
me?

Suppose I have 10 different type of groups to classify a loooong list of
students according to their interests as follows:

A1: A10 B1:B2
I painting
II dancing
.... ...
X reading

My first question is if I can relate text information (I, II, III, IV...X)
with other text info (B1:B10) so I can obtain an automatic fill of one colum
A or B...
I mean if after I write for example painting the opposite colum will
automatically write the group (I) or the opposite: if I introduce the group
i.e. II, inmediately I can know the activity (dancing)?
I had used a replacement tool before, with other kind of tables BUT after
finishing entering all the information in the chart : I mean after finishing
a list I have used the IF (A1:A10="done"), OK, "error"),
how ever this time I can have 10 diferent possibilities "I", "II",
"III"....."X" and I have being giving circles to relate both columns in a
single step.

As the experts of this space might have notice due to my long mail, I have
few experience, but would really appreciate some help in this! ! !

ps: So far I am deciding one by one the student activity, but if you teach
me, my hope is to make a formula in such a way that with a combination of
letters I could know faster the best group to locate each student, by
defining the criterias (i.e. the student will paint if he love painting, is
not interested in physical activities and has a scolarship---> LP, NO Phy and
SCHP...

I will wait to see if someone can help me at the other side of the world.
Thank you,

Karoline

KBZ

 

[Mike Middleton]

Karoline -

Here's a very general non-specific suggestion: Learn just a little bit about
arranging your data in standard Excel database format. Then you can use
Excel's Filter, Advanced Filter, and Pivot Table features.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[Mike H]

Hi,

If I've understood correctly then you could try this. Build a table
somewhere out of the way looking something like this:-

1 Dance
11 Music
111 Song
1V This
V That
VI The
V111 Other
V111 Rock
1X Pop
X Classical

In my case it's in I1 to J10
The this formula in B1
=VLOOKUP(A1,$I$1:$J$10,2,FALSE)

Now if you type your Roman number in A1 it will return the adjacent text
from the table. i.e 1X returns Pop.

Drag the formula down as required.

Mike

 

[Karoline]

OH MY GOD!!!!

IT WORKED! I am so excited!!!
Thanks a LOOOOOOT and I send you a big HUG!!!
You have no Idea how much you helped me!!!

All the best!!!

 

[Karoline]

Thanks for the suggestion Mike. I will keep learning step by step.

 

[Ragdyer]

Now, you mentioned being able to enter *either* a group *or* an interest,
and have the associated value for either one displayed.

The first step is to create a datalist that will create the associations
between the groups and the interests.

Locate this in an out-of-the-way area, say Y1 to Z10.
For this example, let's use in Y1 to Y10 regular numbers, 1 to 10.
In Z1 to Z10 list the various interests.

Say you enter a value from either category in A1, and you wish the
affiliated data to display in B1.

So, use this formula in B1:

=IF(A1="","",IF(ISNA(MATCH(A1,Y1:Y10,0)),IF(ISNA(MATCH(A1,Z1:Z10,0)),"NO
Match",
INDEX(Y1:Y10,MATCH(A1,Z1:Z10,0))),INDEX(Z1:Z10,MATCH(A1,Y1:Y10,0))))

This should poll your datalist in *either* direction.

 

[Karoline]

Mmmm interesting approach and step by step explanation. Thanks Ragdyer.

It seems to be that there are multiple ways to reach the result, though I
must confessed using the VLOOKUP strategy suggested by Mike H works better
for amateurs like me, since it reduce the probability to make a mistake while
inserting the data.

How ever thank you very much for taking your time and sharing your knowledge!

 

[Mike H]

you seem to be pleased with my suggestion and I'm glad I could help. thank
you for the feedback.

 

[Pete_UK]

As you use Excel more, you will discover some of the drawbacks with
the formula that Mike gave you. Suppose you enter a value in column A
that is not in the lookup table, like "A" or 123? The formula returns
the error #N/A which means that the value can't be found, but you
might want to show something else instead of the error. Another
situation is that you might have copied the formula down a number of
rows in anticipation of entering data later, but again a blank doesn't
exist in the lookup table so you will get an error.

Ragdyer's formula takes account of these possible errors (and more),
and returns the message "No match" instead of the rather unhelpful #N/
A. I suggest you study it for future use.

Pete

 

[Karoline]

Ok Pete!
I will take note of it! Actually I tried with Ragdyer's formula (i messed
up a bit, but finally got it) and understood your point. Thanks and thanks
Ragdyer too.

 

[Ragdyer]

You're welcome Karoline, and appreciate the feed-back.

And Pete ... What you would you like as a retainer, since you appear to be
acting as my lawyer?<bg>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

Ok Pete!
I will take note of it! Actually I tried with Ragdyer's formula (i messed
up a bit, but finally got it) and understood your point. Thanks and thanks
Ragdyer too.

 

[Pete_UK]

Ha Ha

Karoline seemed to be dismissing your formula as too complex for her,
so I just felt I needed to point out some of the extra things that
your's did compared to Mike's (which is still a fine formula !!)

Pete.