Results 1 to 15 of 27
Thread: Help with ifthen issue

20130605, 16:53 #1
 Join Date
 Jun 2013
 Posts
 17
 Thanks
 14
 Thanked 1 Time in 1 Post
Help with ifthen issue
FOR EXCEL 2010:
I have 6 cells in a row where I am asking people to input numbers. Each cell represents a question number on a survey . For example, the prompt is "Please enter the item numbers (ONE PER CELL) that were scored a 3 or 4" and respondents enter numbers in up to six cells. There is only number 16 that would be in any cell, but it is possible someone may have no inputs, all 6 cells filled, or some combination of number 16.
I am trying to do if/then type formula where the numbers they input correspond to texts and would ultimately plug into a sentence. For example, if any of the 6 cells contain a 1 (e.g. 1st question) then I want the text to be "organization". However, if there is MORE than just 1 (e.g. than one of the 6 cells with a number" it needs to be "organization, ". If only two cells are filled, it needs to say "organization and ". If its only one cell filled with 1, it would say "organization." You can see how with 36+ combinations this gets a bit tricky and thats if I knew what I was doing in the first place!
Ultimately, what I am trying to do is get this cell(s) to generate so I can automatically plug it into a sentence from another cell. To continue with the example, if someone inputs all 6 numbers, then I would get a text ouptut that says "organization, TEXT2, TEXT3, TEXT4, TEXT5, and TEXT 6." However, if they only put in a few numbers it might be looking like "organization, TEXT3, TEXT4, and TEXT6."
I hope this is clear enough...i'm not even really sure its possible but thought it wouldnt hurt to try. I uploaded a file example to help clarify.
p.s. excel and forum noobie so speak slowly lolLast edited by kissingfrogs2003; 20130605 at 16:58.

20130605, 18:37 #2
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 1,501
 Thanks
 60
 Thanked 304 Times in 279 Posts
Build expression with VBA
kf2003,
The following code will build your expressions in cell A18 based on the score values entered in cells B7 through G7. The code will update the expression with each entry into the range of cells. To prevent blanks in the sentence, the scores will be sorted horizontally when entered. Code goes in the Worksheet_Change event subroutine of the worksheet.
HTH,
Maud
SENTENCE.jpg
Code:Private Sub Worksheet_Change(ByVal Target As Range) Dim trait(1 To 6) 'CREATE AN ARRAY VARIABLE Application.ScreenUpdating = False Application.EnableEvents = False 'SORT SCORES ActiveWorkbook.Worksheets("ENTER DATA HERE").Sort.SortFields.Clear ActiveWorkbook.Worksheets("ENTER DATA HERE").Sort.SortFields.Add Key:=Range("B7:G7") With ActiveWorkbook.Worksheets("ENTER DATA HERE").Sort .SetRange Range("B7:G7") .Orientation = xlLeftToRight .Apply End With 'ASSIGN ARRAY VARIABLE THE VALUE OF CELLS IN RANGE B7 TO G7 For I = 1 To 6 Select Case Cells(7, I + 1).Value Case 1 trait(I) = "organization" Case 2 trait(I) = "procrastination" Case 3 trait(I) = "distractibility" Case 4 trait(I) = "inability to sit still" Case 5 trait(I) = "fidgeting" Case 6 trait(I) = "difficulty with turn taking" Case Else trait(I) = "" End Select Next I 'BUILD EXPRESSION num = WorksheetFunction.CountIf(Range("B7:G7"), ">=1")  WorksheetFunction.CountIf(Range("B7:G7"), ">6") 'COUNT NUMBER OF TRAITS phrase = "Items with highest endorsement indicate trouble with (" 'SET NON CHANGING PART OF EXPRESSION TO VARIABLE Select Case num 'BUILD EXPRESSION BASED ON NUMBER OF TRAITS Case 0 Cells(18, 1).Value = phrase & "none)" Case 1 Cells(18, 1).Value = phrase & trait(1) & ")" Case 2 Cells(18, 1).Value = phrase & trait(1) & " and " & trait(2) & ")" Case 3 Cells(18, 1).Value = phrase & trait(1) & ", " & trait(2) & " , and " & trait(3) & ")" Case 4 Cells(18, 1).Value = phrase & trait(1) & ", " & trait(2) & ", " & trait(3) & " , and " & trait(4) & ")" Case 5 Cells(18, 1).Value = phrase & trait(1) & ", " & trait(2) & ", " & trait(3) & ", " & trait(4) & " , and " & trait(5) & ")" Case 6 Cells(18, 1).Value = phrase & trait(1) & ", " & trait(2) & ", " & trait(3) & ", " & trait(4) & ", " & trait(5) & " , and " & trait(6) & ")" End Select 'BOLD THE TRAIT SECTION OF EXPRESSION num = Len(Cells(18, 1).Value) With Cells(18, 1).Characters(Start:=55, Length:=num).Font .FontStyle = "Bold" End With Application.EnableEvents = True Application.ScreenUpdating = True End Sub
Last edited by Maudibe; 20130605 at 18:53.

The Following User Says Thank You to Maudibe For This Useful Post:
kissingfrogs2003 (20130607)

20130605, 20:00 #3
 Join Date
 Jan 2001
 Location
 La Jolla,CA
 Posts
 1,133
 Thanks
 15
 Thanked 38 Times in 37 Posts
Kissingfrogs2003, you sure have some good ones! Isn't Maud's VB just the bomb? NICE.
But, I just had to try this without VB. Look at this mess in C18.
Did I do the right thing? WHEW!

The Following User Says Thank You to kweaver For This Useful Post:
kissingfrogs2003 (20130607)

20130605, 21:30 #4
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 1,501
 Thanks
 60
 Thanked 304 Times in 279 Posts
=(IF(ISBLANK(B7),"",INDEX(A11:A16,B7)&IF(1=6COUNTIF(B7:G7,""),"",IF(2=6COUNTIF(B7:G7,"")," and ",", ")))&IF(ISBLANK(C7),"",INDEX(A11:A16,C7)&IF(1= 5COUNTIF(C7:G7,""),"",IF(2=5COUNTIF(C7:G7,"")," and ",", ")))&IF(ISBLANK(D7),"",INDEX(A11:A16,D7)&IF(1= 4COUNTIF(D7:G7,""),"",IF(2=4COUNTIF(D7:G7,"")," and ",", ")))&IF(ISBLANK(E7),"",INDEX(A11:A16,E7)&IF(1= 3COUNTIF(E7:G7,""),"",IF(2=3COUNTIF(E7:G7,"")," and ",", ")))&IF(ISBLANK(F7),"",INDEX(A11:A16,F7)&IF(1= 2COUNTIF(F7:G7,""),"",IF(2=2COUNTIF(F7:G7,"")," and ",", ")))&IF(ISBLANK(G7),"",INDEX(A11:A16,G7)&IF(1= 1COUNTIF(G7:G7,""),"",IF(2=1COUNTIF(G7:G7,"")," and ",", "))))&"."

The Following User Says Thank You to Maudibe For This Useful Post:
kissingfrogs2003 (20130607)

20130605, 21:42 #5
 Join Date
 Jan 2001
 Location
 La Jolla,CA
 Posts
 1,133
 Thanks
 15
 Thanked 38 Times in 37 Posts
I wish CONCATENATE worked differently/better, so that you could take an array and concatenate something on the front or back of the entire array. I use a programming language called APL which is heavily ARRAY processing, and actually this would have been been trivial in APL.

The Following User Says Thank You to kweaver For This Useful Post:
kissingfrogs2003 (20130607)

20130606, 13:27 #6
 Join Date
 Jun 2013
 Posts
 17
 Thanks
 14
 Thanked 1 Time in 1 Post
WOW Maud that is awesome...one problem. I HAVE NO IDEA ANYTHING ABOUT CODE!!! o_O
How do I put this in? where do I put this in? o_O
sorry for all the confusion and trouble...but rest assured the questions will become less and less as you all teach me more and more

20130606, 13:39 #7
 Join Date
 Jun 2013
 Posts
 17
 Thanks
 14
 Thanked 1 Time in 1 Post
Also, I tried doing the code from c18 replacing using locations from my actual spreadsheet (which has WAY more than the same I sent you)...I got an error saying "you've entered too many arguments for this function". Here is EXCATLY what I have in my cell...*look out now!*
=(IF(ISBLANK('ENTER DATA HERE'!B21),"",INDEX('DO NOT EDIT...FORMULAS!!'!A37:A42,'ENTER DATA HERE'!B21)&IF(1=6COUNTIF('ENTER DATA HERE'!B21:G21,""),"",IF(2=6COUNTIF('ENTER DATA HERE'!B21:G21,"")," and ",", ")))&IF(ISBLANK('ENTER DATA HERE'!C21),"",INDEX('DO NOT EDIT...FORMULAS!!'!A37:A42,'ENTER DATA HERE'!C21)&IF(1=5COUNTIF('ENTER DATA HERE'!C21:G21,""),"",IF(2=5COUNTIF('ENTER DATA HERE'!C21:G21,"")," and ",", ")))&IF(ISBLANK('ENTER DATA HERE'!D21),"",INDEX('DO NOT EDIT...FORMULAS!!'!A37:A42,'ENTER DATA HERE'!D21)&IF(1=4COUNTIF('ENTER DATA HERE'!D21:G21,""),"",IF(2=4COUNTIF('ENTER DATA HERE'!D21:G21,"")," and ",", ")))&IF(ISBLANK('ENTER DATA HERE'!E21),"",INDEX('DO NOT EDIT...FORMULAS!!'!A37:A42,'ENTER DATA HERE'!E21)&IF(1=3COUNTIF('ENTER DATA HERE'!E21:G21,""),"",IF(2=3COUNTIF('ENTER DATA HERE'!E21:G21,"")," and ",", ")))&IF(ISBLANK('ENTER DATA HERE'!F21,"",INDEX('DO NOT EDIT...FORMULAS!!'!A37:A42,'ENTER DATA HERE'!F21)&IF(1=2COUNTIF('ENTER DATA HERE'!F21:G21,""),"",IF(2=2COUNTIF('ENTER DATA HERE'!F21:G21,"")," and ",", ")))&IF(ISBLANK('ENTER DATA HERE'!G21),"",INDEX('ENTER DATA HERE'!A37:A42,'ENTER DATA HERE'!G21)&IF(1=1COUNTIF('ENTER DATA HERE'!G21:G21,""),"",IF(2=1COUNTIF('ENTER DATA HERE'!G21:G21,"")," and ",", "))))&"."

20130606, 13:48 #8
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 1,501
 Thanks
 60
 Thanked 304 Times in 279 Posts
KF,
I would first try Kweaver's formula first to see if that works for you. It is easier to apply. If not, look at the sample file I provided. Open the VB editor by pressing AltF11. Double Click on Sheet1 (ENTER DATA HERE). You will see a code window open on right if not already visible. Highlight from top of sheet to last line of code, right click and copy. You can paste it in notepad if you like to hold it there. Close the document and open your main workbook. Press AltF11 and the VB editor will again open. Double click your main sheet to open its code window and paste the code. Note: If you have any macros already present with the same name, it will cause an error. I am assuming you do not have any macros present.
IMPORTANT: You must now save this document as an .xlsm file because it contains macros. You can still use the same name however.
Remeber, the code is configured to work in those cells. If you are using different cells then the cells in the code need to be changed as well.
HTH,
MaudLast edited by Maudibe; 20130606 at 13:51.

The Following User Says Thank You to Maudibe For This Useful Post:
kissingfrogs2003 (20130607)

20130606, 14:17 #9
 Join Date
 Jun 2013
 Posts
 17
 Thanks
 14
 Thanked 1 Time in 1 Post
I tried using Kweavers formula and it gave me the error I posted about above your comment (copied below)....

Also, I tried doing the code from c18 replacing using locations from my actual spreadsheet (which has WAY more than the same I sent you)...I got an error saying "you've entered too many arguments for this function". Here is EXCATLY what I have in my cell...*look out now!*
=(IF(ISBLANK('ENTER DATA HERE'!B21),"",INDEX('DO NOT EDIT...FORMULAS!!'!A37:A42,'ENTER DATA HERE'!B21)&IF(1=6COUNTIF('ENTER DATA HERE'!B21:G21,""),"",IF(2=6COUNTIF('ENTER DATA HERE'!B21:G21,"")," and ",", ")))&IF(ISBLANK('ENTER DATA HERE'!C21),"",INDEX('DO NOT EDIT...FORMULAS!!'!A37:A42,'ENTER DATA HERE'!C21)&IF(1=5COUNTIF('ENTER DATA HERE'!C21:G21,""),"",IF(2=5COUNTIF('ENTER DATA HERE'!C21:G21,"")," and ",", ")))&IF(ISBLANK('ENTER DATA HERE'!D21),"",INDEX('DO NOT EDIT...FORMULAS!!'!A37:A42,'ENTER DATA HERE'!D21)&IF(1=4COUNTIF('ENTER DATA HERE'!D21:G21,""),"",IF(2=4COUNTIF('ENTER DATA HERE'!D21:G21,"")," and ",", ")))&IF(ISBLANK('ENTER DATA HERE'!E21),"",INDEX('DO NOT EDIT...FORMULAS!!'!A37:A42,'ENTER DATA HERE'!E21)&IF(1=3COUNTIF('ENTER DATA HERE'!E21:G21,""),"",IF(2=3COUNTIF('ENTER DATA HERE'!E21:G21,"")," and ",", ")))&IF(ISBLANK('ENTER DATA HERE'!F21,"",INDEX('DO NOT EDIT...FORMULAS!!'!A37:A42,'ENTER DATA HERE'!F21)&IF(1=2COUNTIF('ENTER DATA HERE'!F21:G21,""),"",IF(2=2COUNTIF('ENTER DATA HERE'!F21:G21,"")," and ",", ")))&IF(ISBLANK('ENTER DATA HERE'!G21),"",INDEX('ENTER DATA HERE'!A37:A42,'ENTER DATA HERE'!G21)&IF(1=1COUNTIF('ENTER DATA HERE'!G21:G21,""),"",IF(2=1COUNTIF('ENTER DATA HERE'!G21:G21,"")," and ",", "))))&"."

20130606, 14:56 #10
 Join Date
 Jan 2001
 Location
 La Jolla,CA
 Posts
 1,133
 Thanks
 15
 Thanked 38 Times in 37 Posts
KF,
You were missing a paren. Try this:
=(IF(ISBLANK('ENTER DATA HERE'!B21),"",INDEX('DO NOT EDIT...FORMULAS!!'!A37:A42,'ENTER DATA HERE'!B21)&IF(1=6COUNTIF('ENTER DATA HERE'!B21:G21,""),"",IF(2=6COUNTIF('ENTER DATA HERE'!B21:G21,"")," and ",", ")))&IF(ISBLANK('ENTER DATA HERE'!C21),"",INDEX('DO NOT EDIT...FORMULAS!!'!A37:A42,'ENTER DATA HERE'!C21)&IF(1=5COUNTIF('ENTER DATA HERE'!C21:G21,""),"",IF(2=5COUNTIF('ENTER DATA HERE'!C21:G21,"")," and ",", ")))&IF(ISBLANK('ENTER DATA HERE'!D21),"",INDEX('DO NOT EDIT...FORMULAS!!'!A37:A42,'ENTER DATA HERE'!D21)&IF(1=4COUNTIF('ENTER DATA HERE'!D21:G21,""),"",IF(2=4COUNTIF('ENTER DATA HERE'!D21:G21,"")," and ",", ")))&IF(ISBLANK('ENTER DATA HERE'!E21),"",INDEX('DO NOT EDIT...FORMULAS!!'!A37:A42,'ENTER DATA HERE'!E21)&IF(1=3COUNTIF('ENTER DATA HERE'!E21:G21,""),"",IF(2=3COUNTIF('ENTER DATA HERE'!E21:G21,"")," and ",", ")))&IF(ISBLANK('ENTER DATA HERE'!F21),"",INDEX('DO NOT EDIT...FORMULAS!!'!A37:A42,'ENTER DATA HERE'!F21)&IF(1=2COUNTIF('ENTER DATA HERE'!F21:G21,""),"",IF(2=2COUNTIF('ENTER DATA HERE'!F21:G21,"")," and ",", ")))&IF(ISBLANK('ENTER DATA HERE'!G21),"",INDEX('ENTER DATA HERE'!A37:A42,'ENTER DATA HERE'!G21)&IF(1=1COUNTIF('ENTER DATA HERE'!G21:G21,""),"",IF(2=1COUNTIF('ENTER DATA HERE'!G21:G21,"")," and ",", "))))&"."

The Following User Says Thank You to kweaver For This Useful Post:
kissingfrogs2003 (20130607)

20130606, 17:34 #11
 Join Date
 Mar 2002
 Location
 Newcastle, UK
 Posts
 1,651
 Thanks
 51
 Thanked 205 Times in 199 Posts
Hi all
Blimey! I think it might be easier using a custom function!
In my attached file I have a custom function that will concatenate a range of cells.
This merge function will ignore any blank cells in the specified range, and will allow you to specify a 'join separator', for example a comma and space.
I have added a data validation for row 7 of the sample data supplied.
I left in the long formula from kweaver to show what happens if the same number is entered in row 7.
And just for fun, I added a 'sorted' version of the custom concatenate function to show the joined data in sorted order.
Well, I thought you might like another way of doing this.
zeddy

The Following User Says Thank You to zeddy For This Useful Post:
kissingfrogs2003 (20130607)

20130606, 19:02 #12
 Join Date
 Jun 2013
 Posts
 17
 Thanks
 14
 Thanked 1 Time in 1 Post
Holy cow I'm pretty sure I don't understand what I am doing but you guys are AMAZING geniuses! I'm learning a lot...but I'll admit this one was pretty mucha cut and paste job.
Regardless it works AMAZING (i ended up going with Kweafer's solution since I understood it the most...which isnt saying much). THANK YOU A MILLION TIMES OVER!!!

20130606, 19:14 #13
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 1,501
 Thanks
 60
 Thanked 304 Times in 279 Posts
Nice job zeddy! Just goes to show that there is more than one way to skin a cat!
KF,
Rule of tumb. If you can do it without VB then do it.... but there will come a time when you will need it an when you learn it, you can't live without it!
Good luck,
Maud

The Following User Says Thank You to Maudibe For This Useful Post:
kissingfrogs2003 (20130607)

20130606, 19:44 #14
 Join Date
 Jun 2013
 Posts
 17
 Thanks
 14
 Thanked 1 Time in 1 Post
Ok I lied...the way I tried (copied below) leaves out the last part of the sentence if there is all 6 items inputted! Output is " organization, procrastination, distractibility, inability to sit still, fidgeting and ."
Help?
=(IF(ISBLANK('ENTER DATA HERE'!B15),"",INDEX('DO NOT EDIT...FORMULAS!!'!A38:A43,'ENTER DATA HERE'!B15)&IF(1=6COUNTIF('ENTER DATA HERE'!B15:G15,""),"",IF(2=6COUNTIF('ENTER DATA HERE'!B15:G15,"")," and ",", ")))&IF(ISBLANK('ENTER DATA HERE'!C15),"",INDEX('DO NOT EDIT...FORMULAS!!'!A38:A43,'ENTER DATA HERE'!C15)&IF(1=5COUNTIF('ENTER DATA HERE'!C15:G15,""),"",IF(2=5COUNTIF('ENTER DATA HERE'!C15:G15,"")," and ",", ")))&IF(ISBLANK('ENTER DATA HERE'!D15),"",INDEX('DO NOT EDIT...FORMULAS!!'!A38:A43,'ENTER DATA HERE'!D15)&IF(1=4COUNTIF('ENTER DATA HERE'!D15:G15,""),"",IF(2=4COUNTIF('ENTER DATA HERE'!D15:G15,"")," and ",", ")))&IF(ISBLANK('ENTER DATA HERE'!E15),"",INDEX('DO NOT EDIT...FORMULAS!!'!A38:A43,'ENTER DATA HERE'!E15)&IF(1=3COUNTIF('ENTER DATA HERE'!E15:G15,""),"",IF(2=3COUNTIF('ENTER DATA HERE'!E15:G15,"")," and ",", ")))&IF(ISBLANK('ENTER DATA HERE'!F15),"",INDEX('DO NOT EDIT...FORMULAS!!'!A38:A43,'ENTER DATA HERE'!F15)&IF(1=2COUNTIF('ENTER DATA HERE'!F15:G15,""),"",IF(2=2COUNTIF('ENTER DATA HERE'!F15:G15,"")," and ",", ")))&IF(ISBLANK('ENTER DATA HERE'!G15),"",INDEX('ENTER DATA HERE'!A38:A43,'ENTER DATA HERE'!G15)&IF(1=1COUNTIF('ENTER DATA HERE'!G15:G15,""),"",IF(2=1COUNTIF('ENTER DATA HERE'!G15:G15,"")," and ",", "))))&"."

20130606, 19:56 #15
 Join Date
 Jun 2013
 Posts
 17
 Thanks
 14
 Thanked 1 Time in 1 Post
also a follow up question...I am trying to apply this same formula/logic to another part of my sheet (despite the ending error I am confident we will fix) and ran into a slightly different problem. This is being run on 6 cells as well but some of them are showing "FALSE" based on the following formula. I'm wondering what I need to fill into the IF formula of these cells to prevent a FALSE and thus, prevent messing up the big long formula from this thread. Here is the formula that produces a false result: =IF('ENTER DATA HERE'!D19>69.5,2)
I've tried "=IF('ENTER DATA HERE'!D19>69.5,2,0)" and "=IF('ENTER DATA HERE'!D19>69.5,2," ")" but both give me a #VALUE! error