|
Multiple Criteria to Classify groups
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 |
Multiple Criteria to Classify groups
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 "Karoline" wrote in message ... 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 |
Multiple Criteria to Classify groups
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" wrote: 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 |
Multiple Criteria to Classify groups
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!!! -- KBZ "Mike H" wrote: 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" wrote: 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 |
Multiple Criteria to Classify groups
Thanks for the suggestion Mike. I will keep learning step by step.
-- KBZ "Mike Middleton" wrote: 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 "Karoline" wrote in message ... 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 |
Multiple Criteria to Classify groups
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(M ATCH(A1,Z1:Z10,0)),"NO Match", INDEX(Y1:Y10,MATCH(A1,Z1:Z10,0))),INDEX(Z1:Z10,MAT CH(A1,Y1:Y10,0)))) This should poll your datalist in *either* direction. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Karoline" wrote in message ... 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!!! -- KBZ "Mike H" wrote: 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" wrote: 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 |
Multiple Criteria to Classify groups
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! -- KBZ "Ragdyer" wrote: 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(M ATCH(A1,Z1:Z10,0)),"NO Match", INDEX(Y1:Y10,MATCH(A1,Z1:Z10,0))),INDEX(Z1:Z10,MAT CH(A1,Y1:Y10,0)))) This should poll your datalist in *either* direction. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Karoline" wrote in message ... 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!!! -- KBZ "Mike H" wrote: 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" wrote: 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 |
Multiple Criteria to Classify groups
you seem to be pleased with my suggestion and I'm glad I could help. thank
you for the feedback. "Karoline" wrote: 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!!! -- KBZ "Mike H" wrote: 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" wrote: 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 |
Multiple Criteria to Classify groups
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 On Jul 5, 11:50*pm, Karoline wrote: 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! -- KBZ "Ragdyer" wrote: 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(M ATCH(A1,Z1:Z10,0)),"NO Match", INDEX(Y1:Y10,MATCH(A1,Z1:Z10,0))),INDEX(Z1:Z10,MAT CH(A1,Y1:Y10,0)))) This should poll your datalist in *either* direction. -- HTH, RD |
Multiple Criteria to Classify groups
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. -- KBZ "Pete_UK" wrote: 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 On Jul 5, 11:50 pm, Karoline wrote: 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! -- KBZ "Ragdyer" wrote: 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(M ATCH(A1,Z1:Z10,0)),"NO Match", INDEX(Y1:Y10,MATCH(A1,Z1:Z10,0))),INDEX(Z1:Z10,MAT CH(A1,Y1:Y10,0)))) This should poll your datalist in *either* direction. -- HTH, RD |
Multiple Criteria to Classify groups
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 ! --------------------------------------------------------------------------- "Karoline" wrote in message ... 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. -- KBZ "Pete_UK" wrote: 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 On Jul 5, 11:50 pm, Karoline wrote: 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! -- KBZ "Ragdyer" wrote: 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(M ATCH(A1,Z1:Z10,0)),"NO Match", INDEX(Y1:Y10,MATCH(A1,Z1:Z10,0))),INDEX(Z1:Z10,MAT CH(A1,Y1:Y10,0)))) This should poll your datalist in *either* direction. -- HTH, RD |
Multiple Criteria to Classify groups
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. On Jul 6, 6:59*am, "Ragdyer" wrote: 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 |
| All times are GMT +1. The time now is 11:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com