66k lines to analyze in Excel

Question

I huge amount of data to analyze! I have a table "Resolved Met" and on column G with some text that contains a server name and table "Server List" with 66k name of servers

I have to analyze if the text contains the server name on table "Server List" and if yes to write the server name in front of the text ( in another cell)

What I did was to go to first line of table "Server List" and look for it on column where the text is with a loop

It took more than 6 hours to analyze everything once the I have 66k serves name and 130k lines of text. Here is my code. Do you have some better idea to make it faster?

Sub ()

i = 1
Sheets("Server List").Select
Range("A1").Select

servername = ActiveCell.Offset(i, 0).Value

Do Until IsEmpty(servername)

    Sheets("Resolved Met").Select

    With Worksheets("Resolved Met").Range("G:G")
        Set server = .find(What:=servername, LookIn:=xlValues)
        If Not server Is Nothing Then
            firstAddress = server.Address
            Range(firstAddress).Select
            ActiveCell.Offset(0, 13) = servername
            Do
                Set server = .FindNext(server)
                If server Is Nothing Then
                    GoTo DoneFinding2
                End If
                SecondAdress = server.Address
                Range(SecondAdress).Select
                ActiveCell.Offset(0, 13) = servername

            Loop While SecondAdress <> firstAddress
        End If
        DoneFinding2:
    End With


    Sheets("Server List").Select
    i = i + 1
    servername = ActiveCell.Offset(i, 0).Value

Loop

You should [avoid Activate and Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — cybernetic.nomad, Feb 19 '19 at 21:06
The `.Find` call is certainly not helping performance much either. I'd consider using a `Dictionary` for a lookup instead. — Comintern, Feb 19 '19 at 21:15
Use a database (MS Access, SQLite for file level DBMS) or data set type program (SAS, Stata, SPSS) or open-source tool (Python pandas, R, Julia). These can hold large amount of data and search without looping — Parfait, Feb 19 '19 at 22:01
Would help a lot to show some examples of the data you're working with. Also - do you need to find all matches, or just the first/last? You code overwrites previous matches as each next match is found... — Tim Williams, Feb 20 '19 at 00:06
@MathieuGuindon Guindon - Sorry, I just started to develop my skill with vba. — Alex DS, Feb 21 '19 at 02:33
@Parfait - I Am learning Python, next ones will be unde this language — Alex DS, Feb 21 '19 at 02:33
@TimWilliams Williams - See Link Bellow. It is an excel with what need to be done. Tks! https://drive.google.com/open?id=1fwTMra9DsqHm0jgXa34j2U3201Zfi5lT — Alex DS, Feb 21 '19 at 02:33
In the longer text , is the server name always surrounded by spaces ? You can use `Like` or `Instr` to check if some text is contained in some other text, but I'm guessing you'd want to be able to distinguish "server3" from "server33" for example. — Tim Williams, Feb 21 '19 at 04:18
@TimWilliams Some time they are surrounded by spaces and sometimes no. See a real comment example and real server names comment - ITM Agent Offline:nzapie60 Server names - nzapie6 and nzapie60 (same example you gave me) I did considered it till now. But server33 will not find server3 so if I sort them Server3 will find server33 and it is wrong, but it will be corrected at the time to find of server33 — Alex DS, Feb 21 '19 at 23:01

Tate Garringer · Answer 1 · 2019-02-26T20:48:29.187

You could utilize a Dictionary for this and achieve much better performance

Sub t()

    Dim dict As Object

    Dim i As Long
    Dim endrow As Long

    Set dict = CreateObject("Scripting.Dictionary")

    With Sheets("Server List")
        endrow = .Range("A" & Rows.Count).End(xlUp).Row

        For i = 2 To endrow
            If .Range("A" & i) <> "" Then
                dict.Add CStr(.Range("A" & i)), .Range("A" & i)
            End If
        Next

    End With

    With Sheets("Resolved Met")
        endrow = .Range("G" & Rows.Count).End(xlUp).Row

        For i = 2 To endrow
            If dict.Exists(CStr(.Range("G" & i))) Then
                .Range("G" & i).Offset(0, 13) = dict(CStr(.Range("G" & i)))
            End If
        Next

    End With

End Sub

EDIT:

The code below is based on your comments and the structure of the data you've attached. It assumes that, like the dataset provided, servername will be separated from random text by a space. I tested this with an expansion of the dataset provided (expanded to 66K server names in Server List and 130K Lines in Resolved Met) and achieved correct results in 372.672 seconds. A bit lengthy, but it's about a 98.3% decrease in run-time when compared against the ~6 hours noted in your previous method.

Sub ServerNameLookup()
    Dim dict As Object

    Dim i As Long
    Dim endrow As Long

    Dim textArr
    Dim iText As Long

    Set dict = CreateObject("Scripting.Dictionary")

    With Sheets("Server List")
        endrow = .Range("A" & Rows.Count).End(xlUp).Row

        For i = 2 To endrow
            If .Range("A" & i) <> "" Then
                dict.Add CStr(.Range("A" & i)), .Range("A" & i)
            End If
        Next

    End With

    With Sheets("Resolved Met")
        endrow = .Range("G" & Rows.Count).End(xlUp).Row

        For i = 2 To endrow
            textArr = Split(.Range("G" & i), " ")
            For iText = LBound(textArr) To UBound(textArr)
                If dict.Exists(CStr(textArr(iText))) Then
                    .Range("G" & i).Offset(0, 13) = dict(CStr(textArr(iText)))
                End If
            Next iText
        Next

    End With
End Sub

Hi Tale! I tried your code, it was really fast, however it does note brought me any results. See that I have a list with server names and column G has has a text that has the server name, for example : "Unable to launch ...on **server name**" See that the server name is among some text, that is why i use the "find" function. I am not familiar with dictionary , but i believe it looks for the exactly same value on G columns, and that is why it did not brought any results. Any other suggestion? — Alex DS, Feb 20 '19 at 20:29
You are correct in that the `Key` must be referenced exactly for this to work. Is the phrasing of the text in Column G consistent such that you could use `CStr(Replace(.Range("G" & i),"Unable to launch ...on ", ""))` to return the server name in question by replacing the preceding text with a blank value? — Tate Garringer, Feb 20 '19 at 21:18
I have done an example. Please see google drive link bellow https://drive.google.com/open?id=1fwTMra9DsqHm0jgXa34j2U3201Zfi5lT — Alex DS, Feb 21 '19 at 02:34
I've added to my answer to accommodate a solution based on the data provided. — Tate Garringer, Feb 26 '19 at 20:49

66k lines to analyze in Excel

1 Answers1