Comparing columns and returning value

Closed
Posts
4
Registration date
Thursday July 4, 2013
Status
Member
Last seen
July 5, 2013
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
i've following:

1. Drop down values from 1 to 5 in column A.
2. Drop down values from A to E in column B.

Ive following matrix (A44:C68):

1 A L
2 A L
3 A L
4 A L
1 B L
2 B L
3 B L
1 C L
2 C L
1 D L
5 A M
4 B M
5 B M
3 C M
4 C M
2 D M
3 D M
1 E M
2 E M
5 C H
4 D H
5 D H
3 E H
4 E H
5 E H

I want a formula in column C, to reflect the sum of column A & B, if falls under criteria "L" to read "L" or if falls under "M" to "M" or if falls under "H" to "H".

for example:

Column A Column B Column C
2 D M
5 E H
1 B L

can some help me?

7 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
SEE THE FILE FOR WHILCH WEB ADDRESS IS GSIVEN BELOW

http://speedy.sh/7EJqt/SATSY-130704.xlsx

A1,B1.C1 are having validaiton list

see the formula in E1

choose different values from validation list both in B 1 C1. E1 will change according
Posts
4
Registration date
Thursday July 4, 2013
Status
Member
Last seen
July 5, 2013

Thanks for your reply. However, i would like A1 and B1 only to have validation list. C1 should indicate the result.

For example:

if A1 is 5 and B1 is E then C1 should indicate "H"

if A1 is 2 and B1 is E then C1 should indicate "M"

if A1 is 2 and B1 is B then C1 should indicate "L"

Pls help, i've been banging my head over this. Thanks.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
FIRST CLEAR c1 (CLEAR ALL0
THEN TYPE THIS FORMULA IN C1

=IF(AND(A1=5,B1="E"),"H",IF(AND(A1=2,B1="E"),"M",IF(AND(A1=2,B1="B"),"L","")))

Now choose A1 and B1 from validatgion list and if you choose the items echich you have indicated in the three choices then automatically you willl get C1 and also the counts in E1
Posts
4
Registration date
Thursday July 4, 2013
Status
Member
Last seen
July 5, 2013

Awesome! thanks. hey i can you fwd me ur email i can drop my excel sheet you can i see what im trying to do. in any case, this is what i want.

A and B column have validation list as below:

A B

1 A
2 B
3 C
4 D
5 E

Above validation list has following combination:

if A1 is 1 and B1 is A then C1 should indicate "L"
if A1 is 2 and B1 is A then C1 should indicate "L"
if A1 is 3 and B1 is A then C1 should indicate "L"
if A1 is 4 and B1 is A then C1 should indicate "L"
if A1 is 1 and B1 is B then C1 should indicate "L"
if A1 is 2 and B1 is B then C1 should indicate "L"
if A1 is 3 and B1 is B then C1 should indicate "L"
if A1 is 1 and B1 is C then C1 should indicate "L"
if A1 is 2 and B1 is C then C1 should indicate "L"
if A1 is 1 and B1 is D then C1 should indicate "L"

if A1 is 5 and B1 is A then C1 should indicate "M"
if A1 is 4 and B1 is B then C1 should indicate "M"
if A1 is 5 and B1 is B then C1 should indicate "M"
if A1 is 3 and B1 is C then C1 should indicate "M"
if A1 is 4 and B1 is C then C1 should indicate "M"
if A1 is 2 and B1 is D then C1 should indicate "M"
if A1 is 3 and B1 is D then C1 should indicate "M"
if A1 is 1 and B1 is E then C1 should indicate "M"
if A1 is 2 and B1 is E then C1 should indicate "M"

if A1 is 5 and B1 is C then C1 should indicate "H"
if A1 is 4 and B1 is D then C1 should indicate "H"
if A1 is 5 and B1 is D then C1 should indicate "H"
if A1 is 3 and B1 is E then C1 should indicate "H"
if A1 is 4 and B1 is E then C1 should indicate "H"
if A1 is 5 and B1 is E then C1 should indicate "H"

This combination is listed in my excel sheet as below in A,B & C column:

1 A L
2 A L
3 A L
4 A L
1 B L
2 B L
3 B L
1 C L
2 C L
1 D L
5 A M
4 B M
5 B M
3 C M
4 C M
2 D M
3 D M
1 E M
2 E M
5 C H
4 D H
5 D H
3 E H
4 E H
5 E H

Thats it! Thanks again.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
I wonder whether such large number of combination the formula will be so complex that you annot modify or change it. besides in some versions there is restsriction of sesven nested if(s). perhpas one can try a macro. are you comfortable with a macro
venkat
&&&&venkat1926&&&&@gmail.com
Posts
4
Registration date
Thursday July 4, 2013
Status
Member
Last seen
July 5, 2013

If the Macros can do the trick, then why not! will it be possible that the formulas will be automatically copied whenever i insert a new row?
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
yes it is possible. but I think that restriction of number nested ifs valid in macro also.

APPEARS TO BE A NEW SOLUTION
See the file given in webpage

http://speedy.sh/YqW6b/SASY-3-130706.xlsx

the data is from rows 21 down (only s ample of your data is taken to explai)
A and B data here and C formulla anad D particular letter

A1 B1 have validations. see C1 and D1 formulas.