Finding value in text string

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

45998-verada3

45999-verada4

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

Reply | Quote

Hi Maud

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

zeddy

Reply | Quote

..I too assumed that you always needed 4 of the signs for each match, so that’s why I just used the *4

zeddy

Reply | Quote

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.

Reply | Quote

..we must await enlightenment from verada then

zeddy

Reply | Quote

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

Reply | Quote

Hi Verada,

I modified my code to take care of those cells that have multiple lines separated with Alt-. 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
[COLOR=”#008000″]’——————————-
‘DECLARE AND SET VARIABLES[/COLOR]
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
[COLOR=”#008000″]’——————————-
‘CYCLE THRU RECOMMENDED ACTIONS[/COLOR]
For Each cell In rng
    With Worksheets(“Template”)
    If UCase(.Cells(cell.row, 2)) = item And InStr(1, cell, strng, vbTextCompare) > 0 Then
[COLOR=”#008000″]’——————————-
‘LOOK AT EACH LINE OF MULTILINE VALUE[/COLOR]
        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
[COLOR=”#008000″]’——————————-
‘ZERO VALUE IF NO MATCH[/COLOR]
If count = 0 Then COUNTOCCUR = 0
End Function

Template Sheet:
46003-verada5

Sign Summary Sheet:
46004-Verada6

Reply | Quote

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

Reply | Quote

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)

46010-verada5

Reply | Quote

Hi Maud

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

Great job with the custom function!

zeddy

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Verada,
From your sample data an assumption was made that the needed count would always be at the end of the recommended action. The code has been modified to find the needed count anywhere in the statement with or without an “x” using INSTR and MID function but assumes that the needed count is always wrapped in parentheses “()”. The removal of the zeros was an easy fix by changing:

Code:

If count = 0 Then COUNTOCCUR = 0 

to

If count = 0 Then COUNTOCCUR = “”

46014-Verada7

HTH,
Maud

Reply | Quote

Thanks Maud,

Thanks for the solution with the =countoccur not showing 0 – thanks perfect. Is there anyway to =sum the results, =sum() does not calculate. If its not an easy solution, no problems, I can do it manually if necessary.

Sorry about the ongoing questions, but some of the goal posts are shifting this end:mad:

Very much appreciated

Regards

Reply | Quote

Hi Verada,

I will try my best. Using the image in post #16, could you change the numbers to how you want the results to display for the data that was entered on the Template sheet?

Maud

Reply | Quote

Hi Maud,

Sorry, I’m not really sure what you need. Basically the template is working great, its the sign summary that I was looking at.

I’d like to be about to sum across =SUM(C3:P3) and fill down and =SUM(C3:C10) and fill right. in the example at post #16, Q3 should return a value of 257 and F1 =66

I hope that helps.

Thanks again

Regards

Reply | Quote

I think you basically just need to change the function type to Variant rather than String, and add a line to CDbl the result.

Reply | Quote

I was thinking using just the VAL() function and type but I will explore rory’s solution as well.

Reply | Quote

Yes, Verada. That’s what I wanted to know

The VAL function worked well. This revision converts the numbers from strings to number values to allow them to be summed.

46019-Verada8

Let me know if you need any additional changes.

Maud

Reply | Quote

Thanks Maud, I had to make a change to your previous code to look for [] instead of (), but when I change the new code to [] it causes a problem and I’m not sure what other changes I need to make for it to work correctly (it work just right in your attachment – thank you so much)

Would you be able to modify the original code below to work with the []

Your assistance is very much appreciated.

Regards

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(“Audit Findings”)
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)
COUNTOCCUR = Right(s(I), 4)
COUNTOCCUR = Replace(COUNTOCCUR, “[“, “”, , , vbTextCompare)
COUNTOCCUR = Replace(COUNTOCCUR, “]”, “”, , , vbTextCompare)
COUNTOCCUR = Replace(COUNTOCCUR, “x”, “”, , , vbTextCompare)
End If
Next I
End If
End With
Next cell
‘——————————-
‘ZERO VALUE IF NO MATCH
If count = 0 Then COUNTOCCUR = “”
End Function

Reply | Quote

Thanks Maud – that looks to be just what I needed.

Your help is very much appreciated – if you ever get to this side of the world I’ll buy you a good australian wine!

Regards

Reply | Quote

I’ll be there Tuesday!

You are most welcome.

Maud

Reply | Quote

Shiraz or Merlot?

Reply | Quote