Comparing columns and returning value
Solved/Closed
Thakkudu
Posts
5
Registration date
Sunday February 17, 2013
Status
Member
Last seen
February 19, 2013
-
Feb 17, 2013 at 07:00 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Aug 20, 2015 at 11:03 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Aug 20, 2015 at 11:03 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
5 responses
Thakkudu
Posts
5
Registration date
Sunday February 17, 2013
Status
Member
Last seen
February 19, 2013
1
Feb 17, 2013 at 11:09 PM
Feb 17, 2013 at 11:09 PM
Hi
Actully, I need to compare 12 columns.(12 months of an year). With IF statement I can use only 7 'Ifs' and the ramining 5 columns remains without comparing.
Actully, I need to compare 12 columns.(12 months of an year). With IF statement I can use only 7 'Ifs' and the ramining 5 columns remains without comparing.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Feb 17, 2013 at 08:44 AM
Feb 17, 2013 at 08:44 AM
look into IF statement
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Aug 20, 2015 at 11:03 AM
Aug 20, 2015 at 11:03 AM
Hi Arlene,
Not sure I understand your question?
You want to replace all the cells containing "False" with its corresponding column header?
Can you give an example and also mention to what range it applies?
Best regards,
Trowa
Not sure I understand your question?
You want to replace all the cells containing "False" with its corresponding column header?
Can you give an example and also mention to what range it applies?
Best regards,
Trowa
Kevin@Radstock
Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
9
Feb 18, 2013 at 02:02 AM
Feb 18, 2013 at 02:02 AM
Hi Thakkudu
For your data in post #1, the following formula should cover that. In D1 & copy down.
=IF(AND(A1>0,COUNTA(B1:C1)=0),140*1,IF(AND(B1>0,C1=""),140*2,""))
Perhaps it would be better if you posted a sample worksheet with dummy data.
Kevin
@ rizvisa1
Are you ever going to post a example of what you suggest!!
For your data in post #1, the following formula should cover that. In D1 & copy down.
=IF(AND(A1>0,COUNTA(B1:C1)=0),140*1,IF(AND(B1>0,C1=""),140*2,""))
Perhaps it would be better if you posted a sample worksheet with dummy data.
Kevin
@ rizvisa1
Are you ever going to post a example of what you suggest!!
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Feb 18, 2013 at 10:57 AM
Feb 18, 2013 at 10:57 AM
@ Kevin@Radstock
What is the point of giving an answer if you don't know the full extent of the query?
Your answer is rubbish considering Thakkudu's reply on Feb 17, 2013 11:09PM.
So it's not a bad idea to ask some questions to get a better idea/understanding of what needs to be solved.
What is the point of giving an answer if you don't know the full extent of the query?
Your answer is rubbish considering Thakkudu's reply on Feb 17, 2013 11:09PM.
So it's not a bad idea to ask some questions to get a better idea/understanding of what needs to be solved.
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Feb 18, 2013 at 10:49 AM
Feb 18, 2013 at 10:49 AM
Hi Thakkudu,
You can use more than 7 if's if you are willing to sacrifise another column.
This formula should be placed where you want the result:
=IF(L2<>"",12*140,IF(K2<>"",11*140,IF(J2<>"",10*140,IF(I2<>"",9*140,IF(H2<>"",8*140,N2)))))
When all of these if statements are false, the formula will look at N2. You can change this to whatever column you want to use. Since you won't be doing anything else with this data the column can be hidden or the text made white.
The formula in N2 (or wherever) will look like:
=IF(G2<>"",7*140,IF(F2<>"",6*140,IF(E2<>"",5*140,IF(D2<>"",4*140,IF(C2<>"",3*140,IF(B2<>"",2*140,IF(A2<>"",1*140,"")))))))
So two formula's working in harmony to give you the desired result.
Best regards,
Trowa
You can use more than 7 if's if you are willing to sacrifise another column.
This formula should be placed where you want the result:
=IF(L2<>"",12*140,IF(K2<>"",11*140,IF(J2<>"",10*140,IF(I2<>"",9*140,IF(H2<>"",8*140,N2)))))
When all of these if statements are false, the formula will look at N2. You can change this to whatever column you want to use. Since you won't be doing anything else with this data the column can be hidden or the text made white.
The formula in N2 (or wherever) will look like:
=IF(G2<>"",7*140,IF(F2<>"",6*140,IF(E2<>"",5*140,IF(D2<>"",4*140,IF(C2<>"",3*140,IF(B2<>"",2*140,IF(A2<>"",1*140,"")))))))
So two formula's working in harmony to give you the desired result.
Best regards,
Trowa
Didn't find the answer you are looking for?
Ask a question
Thakkudu
Posts
5
Registration date
Sunday February 17, 2013
Status
Member
Last seen
February 19, 2013
1
Feb 19, 2013 at 12:48 AM
Feb 19, 2013 at 12:48 AM
Hi Trowa
This worked and is really helpful. I can use this for my other worksheets also.
Thank you sooooooooooooooo much.
regards
This worked and is really helpful. I can use this for my other worksheets also.
Thank you sooooooooooooooo much.
regards
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Feb 19, 2013 at 10:42 AM
Feb 19, 2013 at 10:42 AM
Glad I could help.
Feb 18, 2013 at 06:36 AM
Can you have more than one column Filled in. If yes then then what should happen ?
Feb 18, 2013 at 07:05 AM
You are right. The multiple I use with 140 refers to month.
I have data in more than one column.
I want if column Jan has some value answer should be 140*1
If column Feb has some value answer should be 140*2 and so on until Dec.
Feb 18, 2013 at 07:28 AM
does the header of the column shows date or month?
Feb 18, 2013 at 07:33 AM
For eg: If feb has a data 140*2
If mar has a data 140*3 and so on
the column header shows month
Feb 18, 2013 at 08:09 AM
but lets say your dates start from Column A till Column L
and you want the result in column N
and there are months like JANUARY etc in row 1
then you can try some thing like this
=140 * MONTH("01-" & INDEX(A1:L1,MATCH(9.99999999999999E+307,L2:L2)) & "-2013")