Help to speed up UDF (if is possible)

[davidmg1982]

Hi all, its my first day at work and par of it is to run a formula based on a UDF, which search for a value within a cell text string based on a column list, this list is almost 25000 rows. Since its searching for this many words in almost 35000 text strings, it takes about 40 min, which is honestly fine for the amount of work, but i would appreciate if any of you kind people take a look and sugest any posible improvement to the function to speed the process. Thanks in advance for your kind help.

Example of the formula since i cannot attach an example doc.
B2=ExtractServer(A2,Sheet2!$A$1:$A$22804)

Function ExtractServer(SearchRange As Range, DogRange As Range) As String
 For Each Myrange In DogRange
  If InStr(1, SearchRange.Value, Myrange) > 0 Then ExtractServer = Myrange
 Next
End Function

 

[Rick Rothstein]

[/B]Heres an example file of the data:
https://swa.box.com/s/r23huvpoi4r2pzkobybkysulyvrx6oi4

Thanks for the example file... that will make testing easier.

Question: Do the values on the DOGRANGE sheet change or is that list fixed? If they change, how... do values themselves get changed or do you simply add more values to the end of the current list?

 

[davidmg1982]

Thanks for the example file... that will make testing easier.

Question: Do the values on the DOGRANGE sheet change or is that list fixed? If they change, how... do values themselves get changed or do you simply add more values to the end of the current list?

The DOGRANGE sheet its a report we download with he total of active Servers we have, can be more or less, a fresh DOGRANGE list is added to the workbook every time we need to run the process.

 

[Rick Rothstein]

The DOGRANGE sheet its a report we download with he total of active Servers we have, can be more or less, a fresh DOGRANGE list is added to the workbook every time we need to run the process.

So for any one workbook, the list on the SEARCHNAMES and DOGRANGE sheets remain fixed and unchanging, is that correct? If so, then what do you do now with your UDF... every time you get a new workbook (report), do you put it on a cell on the SEARCHNAMES sheet and then copy it down (is that the process taking up all the time you reported in Message #1)?

 

[davidmg1982]

So for any one workbook, the list on the SEARCHNAMES and DOGRANGE sheets remain fixed and unchanging, is that correct?

SEARCHNAME is a report of the last 3 months incidents, and this list is the Short Description of such incident, so every couple of weeks we download this new report and the number of incidents may vary, and new rows will be added. We need to identify the Server related to this incident (main reason of the process)

=If so, then what do you do now with your UDF...

Because the SEARCHNAMES list is a very large document, and we have a column that already contains the server name if the incident type is server, for the other categories we use the UDF to extract the server name, the formula we use is:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A TYPE[/TD]
[TD]B SERVER[/TD]
[TD]C SHORT DESCRIPTION[/TD]
[TD]D EXTRACT SERVER[/TD]
[/TR]
[TR]
[TD]APPLICATION[/TD]
[TD][/TD]
[TD]9 PMCLCAP JOB PMCLCAP CODA INBOUND PROD 445AM ON XLPFTP02 HAS ABENDED JOB 8863 PLEASE INVESTIGATE KB0020759 XLPFTP02NEW[/TD]
[TD]=IF(A1="SERVER",A1,ExtractServer(C1,Dogrange!$A$1:$A$22804)[/TD]
[/TR]
[TR]
[TD]SERVER[/TD]
[TD]XLPFTP02[/TD]
[TD]9 PMCLCAP JOB PMCLCAP CODA INBOUND PROD 445AM ON XLPFTP02 HAS ABENDED JOB 8863 PLEASE INVESTIGATE KB0020759[/TD]
[TD]=IF(A2="SERVER",A2,ExtractServer(C2,Dogrange!$A$1:$A$22804)[/TD]
[/TR]
</tbody>[/TABLE]

=every time you get a new workbook (report), do you put it on a cell on the SEARCHNAMES sheet and then copy it down (is that the process taking up all the time you reported in Message #1)?

Every two weeks we download the incident report and the server report put together on a file, insert the UDF and copy down.

 

[davidmg1982]

God im making this so difficult for you, i type an error in the formula, if the type is server then brings the server name in the contiguous cell.

=IF(A2="SERVER",B2,ExtractServer(C2,Dogrange!$A$1:$A$22804)

 

[Rick Rothstein]

God im making this so difficult for you, i type an error in the formula, if the type is server then brings the server name in the contiguous cell.

=IF(A2="SERVER",B2,ExtractServer(C2,Dogrange!$A$1:$A$22804)

Some questions...

1) Is what you show in Message #14 your actual layout for the SEARCHNAMES sheet? I ask because it differs from the example file you posted at that link you provided in Message #10 .

2) Do you actually have that header row in Row 1? I ask because your first formula shown in Column D (but which I understand goes in Column B) references cell A1, not A2.

3) If so, am I correct in understanding that the only time you need to call your ExtractServer UDF is when the cell in Column A contains the word "SERVER"?

4) If the word "SERVER" is not in Column A, you want the word in Column A duplicated in Column B, correct? Your table shows it blank, but your formula seems to indicate you are copying the word.

5) Will the word "SERVER" always be in all caps?

6) Any chance you can post an example file with the correct layout with Column A filled in with the correct values? That will make time testing much easier if I have a real example file to work with.

 

[davidmg1982]

=Rick Rothstein;5005734]Some questions...

1) Is what you show in Message #14 your actual layout for the SEARCHNAMES sheet? I ask because it differs from the example file you posted at that link you provided in Message #10 .

Example link below.

2) Do you actually have that header row in Row 1? I ask because your first formula shown in Column D (but which I understand goes in Column B) references cell A1, not A2.

You are correct it contains headers, should be A2

3) If so, am I correct in understanding that the only time you need to call your ExtractServer UDF is when the cell in Column A contains the word "SERVER"?

The opposite, i need the UDF when its other type than Server, because when its server i already have the server name in the contiguous cell (b2)

) If the word "SERVER" is not in Column A, you want the word in Column A duplicated in Column B, correct? Your table shows it blank, but your formula seems to indicate you are copying the word.

When in column A is any other word but server, then column B will always be empty, so we need the UDF to extract the name from the short description

5) Will the word "SERVER" always be in all caps?

Actually no, all values in column A are proper, example link below

6) Any chance you can post an example file with the correct layout with Column A filled in with the correct values? That will make time testing much easier if I have a real example file to work with.

Sure can. This file is axactly what we have.

https://swa.box.com/s/q9g7dlkrltojm8lwsth1xcvoc6uczwuz

 

[Rick Rothstein]

Sure can. This file is axactly what we have.

https://swa.box.com/s/q9g7dlkrltojm8lwsth1xcvoc6uczwuz

When I click that link, I am taken to a folder named "Lynda - Power BI Pro Essential Training" with 12 sub-folders in it. Is your file in there someplace? If so, where?

 

[davidmg1982]

Here: https://swa.box.com/s/r23huvpoi4r2pzkobybkysulyvrx6oi4

 

[Rick Rothstein]

Here: https://swa.box.com/s/r23huvpoi4r2pzkobybkysulyvrx6oi4

Give this macro a try... I believe it will do what you want. It fills Column J with either the server name when Column D has the word "Servers" in it or from the DOGRANGE sheet if one of the names on the DOGRANGE sheet exists in the description (sometimes there is no match, so the Column J cell remains blank for them). This code also list multiple servers when more than one name exists on the DOGRANGE sheet. And best of all, the entire list in Column J was generated in about 5 minutes on my computer (your computer's timing will more than likely vary).

[table="width: 500"]
[tr]
	[td]Sub ExtractServerSubroutine()
  Dim X As Long, MinLen As Long, Dogs As String, Data As Variant, Servers As Variant, Result As Variant, W As Variant
  Dogs = " " & Join(Application.Transpose(Sheets("DOGRANGE").Range("A2", Sheets("DOGRANGE").Cells(Rows.Count, "A").End(xlUp)))) & " "
  With Sheets("SEARCHNAMES")
    Data = .Range("I2", .Cells(Rows.Count, "I").End(xlUp))
    Servers = .Range("D2", .Cells(Rows.Count, "D").End(xlUp).Resize(, 5))
  End With
  MinLen = Evaluate("MIN(LEN(DOGRANGE!A2:A" & Sheets("DOGRANGE").Cells(Rows.Count, "A").End(xlUp).Row & "))")
  ReDim Result(1 To UBound(Data), 1 To 1)
  For X = 1 To UBound(Data)
    If Servers(X, 1) = "Servers" Then
      Result(X, 1) = Servers(X, 5)
    Else
      For Each W In Split(Data(X, 1))
        If Len(W) >= MinLen Then
          If InStr(1, Dogs, " " & W & " ", vbTextCompare) Then
            Result(X, 1) = Result(X, 1) & ", " & W
            If Left(Result(X, 1), 1) = "," Then Result(X, 1) = Mid(Result(X, 1), 3)
          End If
        End If
      Next
    End If
  Next
  Range("J2").Resize(UBound(Result)) = Result
End Sub[/td]
[/tr]
[/table]