Comparing two lists under 6 columns with 32000 rows in Excel

Question

I need to import 2 .s19 files to Excel and then compare across 6 Columns and store the output to a txt file. I have my data from column A to F. My current program doesn't store the data using a dictionary or hashtable. It takes too long to complete as there are around 31k lines in the list 1 and 32k lines in list 2. I would like to use a dictionary to store the data but I am unsure how to do it.

My data is in this format:

 Source Data Dump Data
 A B C D E F
[1] file format(4char) address(6char) data(66char) file format(4char) address(6char) data(66char)

---

1) If the first line (A2) or (D2) starts with "S011" or "S804" skip and read next line.

2) If the address in Column B matches the address in Column E, check if the adjacent data linked to it is also a match ie. if B10 = E10, check if C10 = F10. If there is a mismatch in data output to txt file in this format:

Source Data
Address
A10+B10+C10
Dump Data
Address
D10+E10+F10
Result: NOK

3) If column B address is not found in Column E ,

Output :
Source Data
Address
A10+B10+C10
Result: NOK

4) If column E address is not found in Column B , check if the string of data in column F are all 'F's excluding the last 2 characters. if All F's then output OK if Not All F's then output NOK

Output :
Dump Data
Address
D10+E10+F10
Result: NOK

---

My current code:

Sub Compare()
Worksheets(1).Select
Dim orig_folderpath As String, orig_file As String, orig_filename As String
Dim dump_folderpath As String
Dim OutputPath As String, OutputFilename As String
Dim VarEntry As String
Dim count As Integer
Dim mainWB As Workbook
Dim Result As String
Dim startadd As String, endadd As String, Dec_startadd As String, lgth As Integer
Set mainWB = ActiveWorkbook
dump_folderpath = Range("C2").Value
Application.DisplayAlerts = False
For i = mainWB.Sheets.count To 3 Step -1
 Sheets(i).Delete
Next
Application.DisplayAlerts = True
count = 0
Dim dump_file As String, dump_filename As String
dump_file = dump_folderpath & "\dump_*.s19"
dump_filename = Dir(dump_file)
OutputPath = Range("C4").Value
OutputFilename = Range("E4").Value
VarEntry = Range("C3").Value
Do While dump_filename <> ""
 count = count + 1
 'dump_file_(count) = dump_filename
 dump_filename = Dir()
 mainWB.Sheets.Add(After:=mainWB.Sheets(mainWB.Sheets.count)).Name = "Dump" & count & "_Comparision"
 'Worksheets(count + 1).Select
 'ActiveSheet.Name = "Dump" & count & "_Comparision"
Loop
dump_filename = Dir(dump_file)
ReDim dump_file_(count) As String
For j = 1 To count
 dump_file_(j) = dump_filename
 dump_filename = Dir()
Next
Worksheets(1).Select
orig_folderpath = Range("C1").Value
orig_filename = Range("E1").Value
orig_file = orig_folderpath & "\" & orig_filename & ".s19"
 'ActiveWorkbook.Worksheets.Add
 For k = 1 To count
 Worksheets(k + 2).Select
 Range("A1") = "Original File"
 Range("A2") = "Record type,Byte count"
 Range("B2") = "Address"
 Range("C2") = "Data,Checksum"
 With ActiveSheet.QueryTables.Add(Connection:= _
 "TEXT;" & orig_file, Destination:=Range("$A3ドル"))
 .Name = orig_filename
 .FieldNames = True
 .RowNumbers = False
 .FillAdjacentFormulas = False
 .PreserveFormatting = True
 .RefreshOnFileOpen = False
 .RefreshStyle = xlInsertDeleteCells
 .SavePassword = False
 .SaveData = True
 .AdjustColumnWidth = True
 .RefreshPeriod = 0
 .TextFilePromptOnRefresh = False
 .TextFilePlatform = 437
 .TextFileStartRow = 1
 .TextFileParseType = xlDelimited
 .TextFileTextQualifier = xlTextQualifierDoubleQuote
 .TextFileConsecutiveDelimiter = True
 .TextFileTabDelimiter = False
 .TextFileSemicolonDelimiter = False
 .TextFileCommaDelimiter = False
 .TextFileSpaceDelimiter = True
 .TextFileColumnDataTypes = Array(1)
 .TextFileTrailingMinusNumbers = True
 .Refresh BackgroundQuery:=False
 End With
 Range("A3").Select
 Range(Selection, Selection.End(xlDown)).Select
 Selection.TextToColumns Destination:=Range("A3"), DataType:=xlFixedWidth, _
 FieldInfo:=Array(Array(0, 2), Array(4, 2), Array(10, 2)), TrailingMinusNumbers:= _
 True
 ActiveSheet.Columns().AutoFit
 Worksheets(k + 2).Select
 Range("D1") = "Dump File" & k
 Range("D2") = "Record type,Byte count"
 Range("E2") = "Address"
 Range("F2") = "Data,Checksum"
 Range("F1") = dump_file_(k)
 With ActiveSheet.QueryTables.Add(Connection:= _
 "TEXT;" & dump_folderpath & "\" & dump_file_(k), Destination:=Range("$D3ドル"))
 .Name = dump_file_(k)
 .FieldNames = True
 .RowNumbers = False
 .FillAdjacentFormulas = False
 .PreserveFormatting = True
 .RefreshOnFileOpen = False
 .RefreshStyle = xlInsertDeleteCells
 .SavePassword = False
 .SaveData = True
 .AdjustColumnWidth = True
 .RefreshPeriod = 0
 .TextFilePromptOnRefresh = False
 .TextFilePlatform = 437
 .TextFileStartRow = 1
 .TextFileParseType = xlDelimited
 .TextFileTextQualifier = xlTextQualifierDoubleQuote
 .TextFileConsecutiveDelimiter = True
 .TextFileTabDelimiter = False
 .TextFileSemicolonDelimiter = False
 .TextFileCommaDelimiter = False
 .TextFileSpaceDelimiter = True
 .TextFileColumnDataTypes = Array(1)
 .TextFileTrailingMinusNumbers = True
 .Refresh BackgroundQuery:=False
 End With
 Range("D3").Select
 Range(Selection, Selection.End(xlDown)).Select
 Selection.TextToColumns Destination:=Range("D3"), DataType:=xlFixedWidth, _
 FieldInfo:=Array(Array(0, 2), Array(4, 2), Array(10, 2)), TrailingMinusNumbers:= _
 True
 ActiveSheet.Columns().AutoFit
 Next
y = 2
For x = 1 To count
Worksheets(y + x).Select
'Get a the next available file number
 fn = FreeFile
'Open your file ready for writing.
 'Set fs = CreateObject("Scripting.FileSystemObject")
 'Set a = fs.CreateTextFile(output_file, True)
 output_file = OutputPath & "\" & OutputFilename & x & ".txt"
 Open output_file For Output As #fn
'Set the First row to search from.
 If (Range("A3") = "S011" Or Range("A3") = "S804") Then
 fr = 4
 Else
 fr = 3
 End If
'Find the last row.
 'lr = ActiveCell.SpecialCells(xlLastCell).Row
 lr = Range("A1").End(xlDown).Row
 lr_2 = Range("D1").End(xlDown).Row
 'lr = 20
 'lr_2 = 20
'Set the column for the value that you are searching for.
 sf = "B"
 Debug.Print "sf" & sf
'Set the column for that you are searching in.
 si = "E"
 Debug.Print "si" & si
'You want to search two columns
 'first = 0
 For l = 1 To 2
 Debug.Print "l" & l
'Loop from first row to the last row.
 For cr = fr To lr
 Debug.Print "cr" & cr
'Set the address you're trying to find
 fa = Range(sf & cr).Value
 fa_data = Range(Chr(Asc(sf) + 1) & cr).Value
 Debug.Print "fa" & fa
'Find it
 Set targetcell = Range(si & 3 & ":" & si & lr_2).Find(What:=fa, LookIn:=xlValues, _
 SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
'If Nothing is returned it is not found
 If targetcell Is Nothing Then
'Write your search cell and it's ajacent to your file.
 If l = 1 Then
 startadd = Range(sf & cr).Value
 If startadd <> "FFFFFF" Then
 Dec_startadd = Val("&H" & startadd & "&H")
 lgth = Len(Range(Chr(Asc(sf) + 1) & cr)) - 2
 lgth = lgth / 2 - 1
 endadd = Hex(Dec_startadd + lgth)
 endadd = Right("000000" & endadd, 6)
 Print #fn, "File:" & orig_filename
 Print #fn, "0x" & startadd & " - 0x" & endadd
 Print #fn, Range(Chr(Asc(sf) - 1) & cr).Value & Range(sf & cr).Value & Range(Chr(Asc(sf) + 1) & cr).Value
 Print #fn, "Result: NOK"
 Print #fn,
 Print #fn,
 'Else
 'Print #fn, "File:" & orig_filename
 'Print #fn, "0x" & startadd
 'Print #fn, Range(Chr(Asc(sf) - 1) & cr).Value & Range(sf & cr).Value & Range(Chr(Asc(sf) + 1) & cr).Value
 'Print #fn, "Result: NOK"
 End If
 ElseIf l = 2 Then
 startadd = Range(sf & cr).Value
 Dec_startadd = Val("&H" & startadd & "&H")
 lgth = Len(Range(Chr(Asc(sf) + 1) & cr)) - 2
 lgth = lgth / 2 - 1
 endadd = Hex(Dec_startadd + lgth)
 endadd = Right("000000" & endadd, 6)
 Result = "File:" & dump_file_(x)
 Value = Range(Chr(Asc(sf) + 1) & cr).Value
 myString = Left(Value, Len(Value) - 2)
 Length = Len(myString)
 'Val_Res = Application.WorksheetFunction.CountIf(myString, "F")
 If CheckAllF(myString) = False Then
 If (fa <> "00BFF0") Or (fa <> "00C010") Or (fa <> "00C030") Or (fa <> "00C050") Or (fa <> "00C070") _
 Or (fa <> "00C090") Then
 Print #fn, "File:" & dump_file_(x)
 Print #fn, "0x" & startadd & " - 0x" & endadd
 Print #fn, Range(Chr(Asc(sf) - 1) & cr) & Range(sf & cr) & Range(Chr(Asc(sf) + 1) & cr)
 Print #fn, "Result: NOK"
 Print #fn,
 Print #fn,
 End If
 Else
 If (fa <> "00BFF0") Or (fa <> "00C010") Or (fa <> "00C030") Or (fa <> "00C050") Or (fa <> "00C070") _
 Or (fa <> "00C090") Then
 Print #fn, "File:" & dump_file_(x)
 Print #fn, "0x" & startadd & " - 0x" & endadd
 Print #fn, "Result: OK"
 Print #fn,
 Print #fn,
 End If
 End If
 End If
'If address is found
 Else
 'If Data matches
 If Cells(targetcell.Row, (targetcell.Column) + 1).Value = Cells(cr, Chr(Asc(sf) + 1)).Value Then
 'If Data mismatch
 Else
 If l = 1 Then
 Print #fn, "File:" & orig_filename
 Print #fn, Range(Chr(Asc(sf) - 1) & cr).Value & Range(sf & cr).Value & _
 Range(Chr(Asc(sf) + 1) & cr).Value
 Print #fn, "File:" & dump_file_(x)
 Print #fn, Range(Chr(Asc(si) - 1) & targetcell.Row) & Range(si & targetcell.Row) & _
 Range(Chr(Asc(si) + 1) & targetcell.Row)
 Print #fn, "Result: NOK"
 Print #fn,
 Print #fn,
 End If
 End If
 End If
'I always put a DoEvents in a loop; just in case you need to break out of it.
 DoEvents
 Next
'Now you've done one column swap them over and do it again.
 sf = "E"
 si = "B"
 Debug.Print "sf" & sf
 Debug.Print "si" & si
 If (Range("D3") = "S011" Or Range("D3") = "S804") Then
 fr = 4
 Else
 fr = 3
 End If
 lr_3 = lr
 lr = lr_2
 lr_2 = lr_3
 Next
'Manufacturing Data Check
'Addresses: 00BFF0, 00C010, 00C030, 00C050, 00C070, 00C090
Dim data1 As String, data2 As String, data3 As String, data4 As String, data5 As String, data6 As String, data7 As String, _
 data8 As String, data9 As String, data10 As String, data11 As String
 Set targetcell = Range(E & 3 & ":" & E & lr).Find(What:="00BFF0", LookIn:=xlValues, _
 SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
 'test setting
 'Set targetcell = Range(E & 3 & ":" & E & lr).Find(What:="000000", LookIn:=xlValues, _
 'SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
 d1 = Cells(targetcell.Row, (targetcell.Column) + 1).Value
 d2 = Cells((targetcell.Row) + 1, (targetcell.Column) + 1).Value
 d3 = Cells((targetcell.Row) + 2, (targetcell.Column) + 1).Value
 d4 = Cells((targetcell.Row) + 3, (targetcell.Column) + 1).Value
 d5 = Cells((targetcell.Row) + 4, (targetcell.Column) + 1).Value
 d6 = Cells((targetcell.Row) + 5, (targetcell.Column) + 1).Value
 data1 = Mid(d1, 33, 32)
 data2 = Mid(d2, 1, 32)
 data3 = Mid(d2, 33, 32)
 data4 = Mid(d3, 1, 32)
 data5 = Mid(d3, 33, 32)
 data6 = Mid(d4, 1, 32)
 data7 = Mid(d4, 33, 32)
 data8 = Mid(d5, 1, 32)
 data9 = Mid(d5, 33, 32)
 data10 = Mid(d6, 1, 32)
 data11 = Mid(d6, 33, 32)
 Workbooks.Open Filename:=orig_folderpath & "\manufacturing input file.xls"
Dim line1 As String, line2 As String, line3 As String, line4 As String, line5 As String, line6 As String, line7 As String, _
line8 As String, line9 As String, line10 As String, line As String
 Set tcell = Range("A:A").Find(What:=VarEntry, LookIn:=xlValues, _
 SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
 For l = 1 To 16
 line1 = line1 & Right(Cells(tcell.Row + 2, tcell.Column + l), 2)
 Next
 For l = 1 To 16
 line2 = line2 & Right(Cells(tcell.Row + 3, tcell.Column + l), 2)
 Next
 For l = 1 To 16
 line3 = line3 & Right(Cells(tcell.Row + 4, tcell.Column + l), 2)
 Next
 For l = 1 To 16
 line4 = line4 & Right(Cells(tcell.Row + 5, tcell.Column + l), 2)
 Next
 For l = 1 To 16
 line5 = line5 & Right(Cells(tcell.Row + 6, tcell.Column + l), 2)
 Next
 For l = 1 To 16
 line6 = line6 & Right(Cells(tcell.Row + 7, tcell.Column + l), 2)
 Next
 For l = 1 To 16
 line7 = line7 & Right(Cells(tcell.Row + 8, tcell.Column + l), 2)
 Next
 For l = 1 To 16
 line8 = line8 & Right(Cells(tcell.Row + 9, tcell.Column + l), 2)
 Next
 For l = 1 To 16
 line9 = line9 & Right(Cells(tcell.Row + 10, tcell.Column + l), 2)
 Next
 For l = 1 To 16
 line10 = line10 & Right(Cells(tcell.Row + 11, tcell.Column + l), 2)
 Next
 For l = 1 To 16
 line11 = line11 & Right(Cells(tcell.Row + 12, tcell.Column + l), 2)
 Next
 line1 = Replace(line1, "X", "?")
 line2 = Replace(line2, "X", "?")
 line3 = Replace(line3, "X", "?")
 line4 = Replace(line4, "X", "?")
 line5 = Replace(line5, "X", "?")
 line6 = Replace(line6, "X", "?")
 line7 = Replace(line7, "X", "?")
 line8 = Replace(line8, "X", "?")
 line9 = Replace(line9, "X", "?")
 line10 = Replace(line10, "X", "?")
 line11 = Replace(line11, "X", "?")
 ActiveWorkbook.Close SaveChanges:=False
 'test setting
 'data1 = "01000000000000000000002020202020"
 'data2 = "FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF"
 Dim sMatch As Boolean, sMatch1 As Boolean, sMatch2 As Boolean, sMatch3 As Boolean, sMatch4 As Boolean, sMatch5 As Boolean, _
 sMatch6 As Boolean, sMatch7 As Boolean, sMatch8 As Boolean, sMatch9 As Boolean, sMatch10 As Boolean, sMatch11 As Boolean
 sMatch1 = data1 Like line1
 sMatch2 = data2 Like line2
 sMatch3 = data3 Like line3
 sMatch4 = data4 Like line4
 sMatch5 = data5 Like line5
 sMatch6 = data6 Like line6
 sMatch7 = data7 Like line7
 sMatch8 = data8 Like line8
 sMatch9 = data9 Like line9
 sMatch10 = data10 Like line10
 sMatch11 = data11 Like line11
 Debug.Print data1 & " " & line1
 Debug.Print data2 & " " & line2
 Debug.Print data3 & " " & line3
 Debug.Print data4 & " " & line4
 Debug.Print data5 & " " & line5
 Debug.Print data6 & " " & line6
 Debug.Print data7 & " " & line7
 Debug.Print data8 & " " & line8
 Debug.Print data9 & " " & line9
 Debug.Print data10 & " " & line10
 Debug.Print data11 & " " & line11
 Debug.Print "1" & sMatch1, "2" & sMatch2, "3" & sMatch3, "4" & sMatch4, "5" & sMatch5, "6" & sMatch6, "7" & sMatch7, _
 "8" & sMatch8, "9" & sMatch9, "10" & sMatch10, "11" & sMatch11
 sMatch = sMatch1 And sMatch2 And sMatch3 And sMatch4 And sMatch5 And sMatch6 And sMatch7 And sMatch8 And sMatch9 _
 And sMatch10 And sMatch11
 Debug.Print "sMatch: " & sMatch
 Print #fn, "File:" & dump_file_(x)
 Print #fn, "0x00BFF0 - 0x00C0AF"
 For o = 1 To 6
 Print #fn, Cells(targetcell.Row + o - 1, targetcell.Column - 1).Value & Cells(targetcell.Row + o - 1, _
 targetcell.Column).Value & Cells(targetcell.Row + o - 1, targetcell.Column + 1).Value
 Next
 'Manufacturing Data Matches Table
 If sMatch = True Then
 Print #fn, "[MANUFACTURING DATA CHECK]: OK"
 'Manufacturing Data Doesn't Match Table
 Else
 Print #fn, "[MANUFACTURING DATA CHECK]: NOK"
 End If
 Print #fn,
 Print #fn,
 line1 = ""
 line2 = ""
 line3 = ""
 line4 = ""
 line5 = ""
 line6 = ""
 line7 = ""
 line8 = ""
 line9 = ""
 line10 = ""
 line11 = ""
'It's done.
 Close #fn
Next
End Sub

---

Function CheckAllF(ByVal myString As String) As Boolean
Dim isAllF As Boolean
isAllF = True
Dim index As Integer
For index = 1 To Len(myString)
 If (Mid(myString, index, 1) <> "F") Then
 isAllF = False
 Exit For
 End If
Next
CheckAllF = isAllF
End Function

• Phrancis: Welcome to Code Review! I hope you get some great answers.
• Manick9: Is using SQL Server Express a better way to process this data? Thanks! @Phrancis
• Manick9: Well, the code has been written and it takes 30+mins to execute. I understand using a dictionary or array to store data will make the comparing faster.
• Phrancis: it depends, but generally any SQL database engine is highly optimized for processing very large quantities of data. On the flip side it does add maintenance overhead for the database.

Answer 1

Breaking your Code in Subs and Functions: as a first step in trying to optimize, I would recommend splitting this big sub into smaller ones. For instance, this piece of code could be a sub on its own, with a clear purpose and responsibility:

Sub DeleteSheets()
 Application.DisplayAlerts = False
 For i = mainWB.Sheets.count To 3 Step -1
 Sheets(i).Delete
 Next
 Application.DisplayAlerts = True
End Sub

You already split your code with comments and blocks of variable declarations. That should give you a head start on where to extract some code to a new method. This will also help you spotting common pieces of code that you can reuse (which has a lot of benefits). For instance, at the end of the sub you have several constructions that look like this:

For l = 1 To 16
 line1 = line1 & Right(Cells(tcell.Row + 2, tcell.Column + l), 2)
Next

Precedeed with the linex variable declarations and followed by:

line1 = Replace(line1, "X", "?")
....
sMatch1 = data1 Like line1
....
Debug.Print data1 & " " & line1

If you apply what I tell you above, you could easily refactor all those into just one loop (you probably don't need to have 11 variables, because on each loop iteration you are doing everything you need to do to the line variable), and then your code would be easier to read. In the process of doing this, you will also force yourself to understand better the patterns in your code, and then will be able to optimize them more easily.

Variable Names: also, give meaningful names to your variables. lr, si, sf, etc. are not meaningful on their own, and then you need to use comments to explain what each of these variables are. This makes reading your code more difficult for the person reviewing it (someone like me, or even yourself in the future as well), and requires additional elements (in this case, comments), which would be unnecessary if you used proper variable names (that when read, are self-explanatory).

Beware of Macro Recording Generated Code: as it tends to be too verbose, and to include things you don't really require. This is just a hunch, and I might be wrong, but code that looks like this, in several places of your sub, makes me think that you recorded some steps of the process (which is OK), but didn't clean it afterwards. In a performance-sensitive application, this is not good, as it can lead to unnecessary steps being executed over and over:

With ActiveSheet.QueryTables.Add(Connection:= _
 "TEXT;" & dump_folderpath & "\" & dump_file_(k), Destination:=Range("$D3ドル"))
 .Name = dump_file_(k)
 .FieldNames = True
 .RowNumbers = False
 .FillAdjacentFormulas = False
 .PreserveFormatting = True
 .RefreshOnFileOpen = False
 .RefreshStyle = xlInsertDeleteCells
 .SavePassword = False
 .SaveData = True
 .AdjustColumnWidth = True
 .RefreshPeriod = 0
 .TextFilePromptOnRefresh = False
 .TextFilePlatform = 437
 .TextFileStartRow = 1
 .TextFileParseType = xlDelimited
 .TextFileTextQualifier = xlTextQualifierDoubleQuote
 .TextFileConsecutiveDelimiter = True
 .TextFileTabDelimiter = False
 .TextFileSemicolonDelimiter = False
 .TextFileCommaDelimiter = False
 .TextFileSpaceDelimiter = True
 .TextFileColumnDataTypes = Array(1)
 .TextFileTrailingMinusNumbers = True
 .Refresh BackgroundQuery:=False
End With

So I would suggest you take a look at that code and start removing lines that seem unnecessary, testing your application every step of the way, until you get an streamlined version of the code. (This also applies to code taken from other sources and used in your own applications).

Cache your Heavily-Used Referenced Values: some cells contain values that you need to use in several places, or in loops. For instance targetcell.Row, targetcell.Column, tcell.Row and tcell.Column are heavily used and make your program calculate those reference over and over. Instead of this, create a targetCellRow variable, assign targetcell.Row to it, and use targetCellRow whenever you need to reference the target cell row. Notice that some of these values are used in multiple loops! Again, when performance counts, this things start to pile up.

Cherry on the Cake: finally, here is some code I posted in another thread which you can add to optimize almost any Excel VBA code that is taking too long.

After you apply these changes, then you can see if you still require to use a dictionary, array or any other construct to improve performance even better. The good thing is that at that point, your code will be in a more proper state to spot and apply required optimizations.

• Manick9: I am looking for list manipulation. Is there such a feature in VBA ? Would you recommend other languages or software to accomplish the task. You seem like an expert @carlossierra.
• carlossierra: No expert by any means. Trying to get there :)
• carlossierra: Anyway, I would suggest you start applying these suggestions and then evaluating if performance is good enough. I've managed to process fairly large amounts of data in Excel, so if that's what you know, start there. You could also achieve this in many other languages (even Windows Batch File comes to mind), but as said, I wouldn't go elsewhere just yet.
• carlossierra: By the way, did you try my "Cherry on the Cake" suggestion? That alone can make a huge difference. Try that one now. You don't need to modify your existing code to do it, except by adding a couple of lines at the beginning and end of your sub, and then copy-pasting my subs.
• carlossierra: You can post the code here in CR, in a new thread, so that the C# experts can help you. As to your other question, I think that you probably need to ask yourself if you are going to have to maintain this program afterwards, and if so, which language your are more comfortable/familiar with. If you don't need to maintain it afterwards (which is probably unlikely), and being practical, you should probably go with the one that will take you less time/effort.