Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Finding value in text string

    Hi All

    This is a bit difficult to explain.

    I have been provided with a spreadsheet template that I can't change.

    In the attached example in the template worksheet you can see some lines of text in cell I4.

    In the sign summary worksheet you can see that I have set up a formula that counts the occurrence of the words "No unauthorised Entry" this works fine, however (and here is the tricky part - not even sure if this is possible), but I need to capture that I need 4 of them as shown in the Sign Summary worksheet J4.

    Have any of the experts out there got any ideas on how this could be achieved (if at all?)

    I hope that makes sense.

    Any suggestions/solutions would be much appreciated

    Regards
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi verada

    ..just change the formula to:
    Code:
    =IF($B3="","",IF(J2="","",COUNTIFS(Template!$B$4:$B$100,$B$3,Template!$I$4:$I$100,"*"&J2&"*")))*4
    zeddy

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi Zeddy,

    Thanks for the suggestion, but sort of defeats the purpose of using a formula in the first place, it seems to me that I might as well just put in the number 4 in the cell.

    In the real spreadsheet there will be numerous (50 -60) entries in the template and the intention of using a formula is to prevent doubling up on on data to reduce the chance of errors between the "Template" and "Sign Summary". Ideally the formula in the "Sign Summary" would pick up the data (based on the countifs) but put 4 (in the example) into the respective cell.

    I'm not even sure if what i'm trying to do is even possible, but I hope that this all make sense.

    Regards

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Verada,

    Here is one possible solution using a UDF. The user defined function will count the occurrences and the captured need amount on one line.

    Place the following code in a standard module:
    Code:
    Public Function COUNTOCCUR(rng As Range) As String
    Application.Volatile
    Dim cell As Range
    Dim col As Integer, count As Integer
    Dim item As String
    col = Application.Caller.Column
    item = Application.Caller.Offset(0, -8).Value
    strng = Cells(2, col)
    count = 0
    For Each cell In rng
        If cell.Offset(0, -7) = item And InStr(1, cell, strng, vbTextCompare) > 0 Then
            count = count + 1
            COUNTOCCUR = count & Right(cell, 4)
        End If
    Next cell
    End Function
    In cell J3, enter the following formula: =COUNTOCCUR(Template!I3:I30) and fill in the Item in B3. This will count all occurrences of "No Unauthorised Entry" on the Template sheet and indicate the number needed

    verada3.png

    verada4.png

    This could also be done like you had set up pulling over a total row by row using a formula that I will post shortly

    HTH,
    Maud
    Attached Files Attached Files
    Last edited by Maudibe; 2016-11-16 at 08:00. Reason: change image

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Maud

    ..but what if one of them was
    • Install 'No Unauthorised Entry' signage (x7)

    zeddy

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    ..I too assumed that you always needed 4 of the signs for each match, so that's why I just used the *4

    zeddy

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Hey zeddy,

    I just assumed that whatever the number that was for one with a certain paired Item (ex. J1) and text (ex. 'No Unauthorised Entry') would be the same for all of the matching pairs. But the code takes the number from the last match.

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    ..we must await enlightenment from verada then

    zeddy

  9. #9
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Hi Maud, zeddy,

    Thanks so much for your assistance so far.

    To help clarify, the number following the text (ex. 'No Unauthorised Entry') would more than likely change for each location.

    ie J1 could be 'No Unauthorised Entry' (2), J2 could be 'Confined Space' (7) & 'No Unauthorised Entry' (1), etc, etc, the number will depend on the requirement for the particular location.

    I hope that helps

    Regards

  10. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Hi Verada,

    I modified my code to take care of those cells that have multiple lines separated with Alt-<Enter>. The code is now generic in that you can place the same exact formula with the same range in every cell (use absolute referencing).

    On the Sign Summary sheet cell C3, enter the formula =COUNTOCCUR(Template!$I$4:$I$31) and copy to L49.
    Returned results are generated in the format count(xneededcount). ex: 2(x3) in which the item/description pair displayed two times with a needed count of three.

    HTH,
    Maud

    In a standard module:
    Code:
    Public Function COUNTOCCUR(rng As Range) As String
    Application.Volatile
    '-------------------------------
    'DECLARE AND SET VARIABLES
    Dim cell As Range
    Dim col As Integer, row As Integer, count As Integer
    Dim item As String
    col = Application.Caller.Column
    row = Application.Caller.row
    item = UCase(Cells(row, 2))
    'item = Application.Caller.Offset(0, -8).Value
    strng = Cells(2, col)
    count = 0
    '-------------------------------
    'CYCLE THRU RECOMMENDED ACTIONS
    For Each cell In rng
        With Worksheets("Template")
        If UCase(.Cells(cell.row, 2)) = item And InStr(1, cell, strng, vbTextCompare) > 0 Then
    '-------------------------------
    'LOOK AT EACH LINE OF MULTILINE VALUE
            s = Split(.Cells(cell.row, 9), Chr(10))
            For I = 0 To UBound(s)
                If InStr(1, s(I), strng, vbTextCompare) > 0 Then
                    count = count + 1
                    COUNTOCCUR = count & Right(s(I), 4)
                End If
            Next I
        End If
        End With
    Next cell
    '-------------------------------
    'ZERO VALUE IF NO MATCH
    If count = 0 Then COUNTOCCUR = 0
    End Function
    Template Sheet:
    verada5.png

    Sign Summary Sheet:
    Verada6.png
    Attached Files Attached Files

  11. #11
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Maud - you are amazing!!

    Is it possible to modify the code to return just the number, ie instead of 1(x4) it just returns the value 4

    Thanks so much

    Regards

  12. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Verada,

    Replace this line of code:
    Code:
                    COUNTOCCUR = count & Right(s(I), 4)
    with these lines of code:
    Code:
                    COUNTOCCUR = Right(s(I), 4)
                    COUNTOCCUR = Replace(COUNTOCCUR, "(", "", , , vbTextCompare)
                    COUNTOCCUR = Replace(COUNTOCCUR, ")", "", , , vbTextCompare)
                    COUNTOCCUR = Replace(COUNTOCCUR, "x", "", , , vbTextCompare)
    verada5.png

  13. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Maud

    ..will this work if there were (x10) or, heaven forbid, (x100) signs required?

    Great job with the custom function!

    zeddy
    Last edited by zeddy; 2016-11-17 at 09:34.

  14. #14
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Yes, because the code is looking for the last 4 characters. So the last 4 of (x100) would still grab x100) then the code removes the "x" and the ")". The only thing that would mess it up would be trailing spaces that could be cured with a Trim function.

  15. #15
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Trim?

    Hi Maud

    This is looking very close.

    Could you please have a look at the attached (Sign Summary) - I guess that this would be where the trim function would be needed as you suggested - would you be able to assist (once again!) with additional code (or formula) to resolve this result.


    In the Sign Summary, the =sum(...:...) does not return a value just "0" and is it possible to hide 0 values in the cells. The way i normally do it (conditional formatting or hide zero values don't seem to work.


    Much appreciated

    Regards
    Attached Files Attached Files
    Last edited by verada; 2016-11-17 at 22:34. Reason: I have sorted out a workaround for the trim issue. Additional query

Page 1 of 2 12 LastLast

Tags for this Thread

Posting Permissions

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