A cell contains either "Yes", "Yes +Comment", "No", "No + Comment", "Comment only", or "No response"
I want to summarise these, so that the neighbouring cell will contain Yes, No, or No response ie.
"Yes" or "Yes +Comment" = "Yes"
"No" or "No + Comment" = "No"
"Comment only" or "No response" = "No response".
Help please, Linda
Last edited by lindaroslin; 09-14-2012 at 01:02 AM.
Let's say cell A2 contains the yes or no or one of them with comment, then put in cell B2:
Formula:
=IF(LEFT(A2;2)="No";"No";"Yes")
This assumes only yes or no are the first word in the cell, no answers like maybe or don't know.
Hi Linda & Welcome to the Forum,
You could build a lookup table and then use
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
=VLOOKUP(A1,H1:I6,2,0)
column H is the comment and column I contains the response.
A1 is the comment you are looking up
Last edited by jeffreybrown; 09-13-2012 at 02:11 PM.
HTH
Regards, Jeff
Use..
=IF(ISNUMBER(SEARCH("Yes",A1)),"yes",IF(OR(ISNUMBER(SEARCH("No response",A1)),NOT(ISNUMBER(SEARCH("No",A1))),"No response"),"No"))
Life's a spreadsheet, Excel!
Say thanks, Click *
Thanks for trying, but I'm sorry, that's not working - it only works for Yes and Yes + comment, not for No, No + Comment, Comment only or No response.
Linda
try this...
=IF(LEFT(A6,3)="Yes","Yes",IF(LEFT(A6,2)="No","No",IF(OR(LEFT(A4,3)<>"Yes",LEFT(A4,2)<>"no",A4=""),"No response","")))
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
SolvedOriginally Posted by FDibbins
Many thanks FDibbins, that works a treat when I substitute the same reference cell for A6 and A4.
I'll now sit quietly and try to understand why it works.
Linda
Glad to hear you have a workable solution Linda and thanks for the feedback
A good way to break down a formula is by using evaluate formula
Place cursor on the formula >> On the ribbon >> Formulas >> Formula Auditing >> Evaluate Formula
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save
glad to help, when you mentioned A4, i saw that it was supposed to be A6, sorry about that, and good catch from your side.
what its doing is testing the 1st 2 or 3 letters for yes or no...and then neither yes or no BUT also has No input. hope that helps?
thx for the star, too
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks