Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    New Lounger
    Join Date
    Jun 2013
    Posts
    17
    Thanks
    14
    Thanked 1 Time in 1 Post

    Help with if-then 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 1-6 that would be in any cell, but it is possible someone may have no inputs, all 6 cells filled, or some combination of number 1-6.

    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 lol
    Attached Files Attached Files
    Last edited by kissingfrogs2003; 2013-06-05 at 15:58.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,107
    Thanks
    39
    Thanked 197 Times in 184 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
    Attached Files Attached Files
    Last edited by Maudibe; 2013-06-05 at 17:53.

  4. The Following User Says Thank You to Maudibe For This Useful Post:

    kissingfrogs2003 (2013-06-07)

  5. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,055
    Thanks
    11
    Thanked 35 Times in 34 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!
    Attached Files Attached Files

  6. The Following User Says Thank You to kweaver For This Useful Post:

    kissingfrogs2003 (2013-06-07)

  7. #4
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,107
    Thanks
    39
    Thanked 197 Times in 184 Posts

    Smile

    =(IF(ISBLANK(B7),"",INDEX(A11:A16,B7)&IF(1=6-COUNTIF(B7:G7,""),"",IF(2=6-COUNTIF(B7:G7,"")," and ",", ")))&IF(ISBLANK(C7),"",INDEX(A11:A16,C7)&IF(1= 5-COUNTIF(C7:G7,""),"",IF(2=5-COUNTIF(C7:G7,"")," and ",", ")))&IF(ISBLANK(D7),"",INDEX(A11:A16,D7)&IF(1= 4-COUNTIF(D7:G7,""),"",IF(2=4-COUNTIF(D7:G7,"")," and ",", ")))&IF(ISBLANK(E7),"",INDEX(A11:A16,E7)&IF(1= 3-COUNTIF(E7:G7,""),"",IF(2=3-COUNTIF(E7:G7,"")," and ",", ")))&IF(ISBLANK(F7),"",INDEX(A11:A16,F7)&IF(1= 2-COUNTIF(F7:G7,""),"",IF(2=2-COUNTIF(F7:G7,"")," and ",", ")))&IF(ISBLANK(G7),"",INDEX(A11:A16,G7)&IF(1= 1-COUNTIF(G7:G7,""),"",IF(2=1-COUNTIF(G7:G7,"")," and ",", "))))&"."
    Wasn't that a song from Mary Poppins? And it works!! How the heck did you do that? I wouldn't even know where to stuff that one in my bag of tricks.

  8. The Following User Says Thank You to Maudibe For This Useful Post:

    kissingfrogs2003 (2013-06-07)

  9. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,055
    Thanks
    11
    Thanked 35 Times in 34 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.

  10. The Following User Says Thank You to kweaver For This Useful Post:

    kissingfrogs2003 (2013-06-07)

  11. #6
    New Lounger
    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

  12. #7
    New Lounger
    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=6-COUNTIF('ENTER DATA HERE'!B21:G21,""),"",IF(2=6-COUNTIF('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=5-COUNTIF('ENTER DATA HERE'!C21:G21,""),"",IF(2=5-COUNTIF('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=4-COUNTIF('ENTER DATA HERE'!D21:G21,""),"",IF(2=4-COUNTIF('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=3-COUNTIF('ENTER DATA HERE'!E21:G21,""),"",IF(2=3-COUNTIF('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=2-COUNTIF('ENTER DATA HERE'!F21:G21,""),"",IF(2=2-COUNTIF('ENTER DATA HERE'!F21:G21,"")," and ",", ")))&IF(ISBLANK('ENTER DATA HERE'!G21),"",INDEX('ENTER DATA HERE'!A37:A42,'ENTER DATA HERE'!G21)&IF(1=1-COUNTIF('ENTER DATA HERE'!G21:G21,""),"",IF(2=1-COUNTIF('ENTER DATA HERE'!G21:G21,"")," and ",", "))))&"."

  13. #8
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,107
    Thanks
    39
    Thanked 197 Times in 184 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 Alt-F11. 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 Alt-F11 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,
    Maud
    Last edited by Maudibe; 2013-06-06 at 12:51.

  14. The Following User Says Thank You to Maudibe For This Useful Post:

    kissingfrogs2003 (2013-06-07)

  15. #9
    New Lounger
    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=6-COUNTIF('ENTER DATA HERE'!B21:G21,""),"",IF(2=6-COUNTIF('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=5-COUNTIF('ENTER DATA HERE'!C21:G21,""),"",IF(2=5-COUNTIF('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=4-COUNTIF('ENTER DATA HERE'!D21:G21,""),"",IF(2=4-COUNTIF('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=3-COUNTIF('ENTER DATA HERE'!E21:G21,""),"",IF(2=3-COUNTIF('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=2-COUNTIF('ENTER DATA HERE'!F21:G21,""),"",IF(2=2-COUNTIF('ENTER DATA HERE'!F21:G21,"")," and ",", ")))&IF(ISBLANK('ENTER DATA HERE'!G21),"",INDEX('ENTER DATA HERE'!A37:A42,'ENTER DATA HERE'!G21)&IF(1=1-COUNTIF('ENTER DATA HERE'!G21:G21,""),"",IF(2=1-COUNTIF('ENTER DATA HERE'!G21:G21,"")," and ",", "))))&"."

  16. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,055
    Thanks
    11
    Thanked 35 Times in 34 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=6-COUNTIF('ENTER DATA HERE'!B21:G21,""),"",IF(2=6-COUNTIF('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=5-COUNTIF('ENTER DATA HERE'!C21:G21,""),"",IF(2=5-COUNTIF('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=4-COUNTIF('ENTER DATA HERE'!D21:G21,""),"",IF(2=4-COUNTIF('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=3-COUNTIF('ENTER DATA HERE'!E21:G21,""),"",IF(2=3-COUNTIF('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=2-COUNTIF('ENTER DATA HERE'!F21:G21,""),"",IF(2=2-COUNTIF('ENTER DATA HERE'!F21:G21,"")," and ",", ")))&IF(ISBLANK('ENTER DATA HERE'!G21),"",INDEX('ENTER DATA HERE'!A37:A42,'ENTER DATA HERE'!G21)&IF(1=1-COUNTIF('ENTER DATA HERE'!G21:G21,""),"",IF(2=1-COUNTIF('ENTER DATA HERE'!G21:G21,"")," and ",", "))))&"."

  17. The Following User Says Thank You to kweaver For This Useful Post:

    kissingfrogs2003 (2013-06-07)

  18. #11
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,474
    Thanks
    22
    Thanked 168 Times in 164 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
    Attached Files Attached Files

  19. The Following User Says Thank You to zeddy For This Useful Post:

    kissingfrogs2003 (2013-06-07)

  20. #12
    New Lounger
    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 A-MAZING 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!!!

  21. #13
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,107
    Thanks
    39
    Thanked 197 Times in 184 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

  22. The Following User Says Thank You to Maudibe For This Useful Post:

    kissingfrogs2003 (2013-06-07)

  23. #14
    New Lounger
    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=6-COUNTIF('ENTER DATA HERE'!B15:G15,""),"",IF(2=6-COUNTIF('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=5-COUNTIF('ENTER DATA HERE'!C15:G15,""),"",IF(2=5-COUNTIF('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=4-COUNTIF('ENTER DATA HERE'!D15:G15,""),"",IF(2=4-COUNTIF('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=3-COUNTIF('ENTER DATA HERE'!E15:G15,""),"",IF(2=3-COUNTIF('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=2-COUNTIF('ENTER DATA HERE'!F15:G15,""),"",IF(2=2-COUNTIF('ENTER DATA HERE'!F15:G15,"")," and ",", ")))&IF(ISBLANK('ENTER DATA HERE'!G15),"",INDEX('ENTER DATA HERE'!A38:A43,'ENTER DATA HERE'!G15)&IF(1=1-COUNTIF('ENTER DATA HERE'!G15:G15,""),"",IF(2=1-COUNTIF('ENTER DATA HERE'!G15:G15,"")," and ",", "))))&"."

  24. #15
    New Lounger
    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •