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 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 6, 2013 at 12:38 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 6, 2013 at 12:38 AM
Related:
- Comparing columns and returning value
- Display two columns in data validation list but return only one - Guide
- If cell contains date then return value ✓ - Excel Forum
- If cell contains specific text then return value in another cell ✓ - Excel Forum
- Excel formula to check if cell contains a date - Excel Forum
- If two cells match return value from third excel ✓ - Excel Forum
7 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 4, 2013 at 08:43 AM
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
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
SATSY
Posts
4
Registration date
Thursday July 4, 2013
Status
Member
Last seen
July 5, 2013
Jul 4, 2013 at 09:09 AM
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.
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.
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 4, 2013 at 10:13 AM
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
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
SATSY
Posts
4
Registration date
Thursday July 4, 2013
Status
Member
Last seen
July 5, 2013
Jul 4, 2013 at 11:29 AM
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.
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.
Didn't find the answer you are looking for?
Ask a question
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 4, 2013 at 10:13 PM
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
venkat
&&&&venkat1926&&&&@gmail.com
SATSY
Posts
4
Registration date
Thursday July 4, 2013
Status
Member
Last seen
July 5, 2013
Jul 5, 2013 at 11:39 PM
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?
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 6, 2013 at 12:38 AM
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
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
