Comparing columns and returning value

Closed
SATSY Posts 4 Registration date Thursday July 4, 2013 Status Member Last seen July 5, 2013 - Jul 4, 2013 at 02:47 AM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 6, 2013 at 12:38 AM
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

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jul 4, 2013 at 08:43 AM
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
0
SATSY Posts 4 Registration date Thursday July 4, 2013 Status Member Last seen July 5, 2013
Jul 4, 2013 at 09:09 AM
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.
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jul 4, 2013 at 10:13 AM
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
0
SATSY Posts 4 Registration date Thursday July 4, 2013 Status Member Last seen July 5, 2013
Jul 4, 2013 at 11:29 AM
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.
0

Didn't find the answer you are looking for?

Ask a question
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jul 4, 2013 at 10:13 PM
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
0
SATSY Posts 4 Registration date Thursday July 4, 2013 Status Member Last seen July 5, 2013
Jul 5, 2013 at 11:39 PM
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?
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jul 6, 2013 at 12:38 AM
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.

does this serve your purpose
0