'Comparing two rows according to specific string
So i've been trying to wrap my head around this for weeks now and have tried many things but nothing that works for my case, I have two sheets with "similar" Structures( same amount of columns and same rows, achieved through MS Power Query). Here is the code, P.S took the foundation from somewhere else:
Function CompareSheets_Adv(sh1Name$, sheet2name$) As Boolean
Dim vstr As String
Dim vData As Variant
Dim vitm As Variant
Dim vArr As Variant
Dim v()
Dim Range1, Range2, Range3 As Range
Dim a As Long
Dim b As Long
Dim c As Long
On Error GoTo CompareSheetsERR
vData = Sheets(sh1Name$).Range("A2:E500").Value
With CreateObject("Scripting.Dictionary")
.CompareMode = 1`
ReDim v(1 To UBound(vData, 2))
For a = 2 To UBound(vData, 1)
For b = 1 To UBound(vData, 2)
vstr = vstr & Chr(2) & vData(a, b)
v(b) = vData(a, b)
Next
.item(vstr) = v
vstr = ""
Next
vData = Sheets(sheet2name$).Range("A2:E500").Value
For a = 2 To UBound(vData, 1)
For b = 1 To UBound(vData, 2)
vstr = vstr & Chr(2) & vData(a, b)
v(b) = vData(a, b)
Next
If .Exists(vstr) Then
.item(vstr) = Empty
Else
.item(vstr) = v
End If
vstr = ""
Next
For Each vitm In .Keys
If IsEmpty(.item(vitm)) Then
.Remove vitm
End If
Next
vArr = .Items
c = .count
End With
With Sheets("Results").Range("a2").Resize(, UBound(vData, 2))
.Cells.Clear
.Value = vData
If c > 0 Then
.Offset(1).Resize(c).Value = Application.Transpose(Application.Transpose(vArr))
End If
End With
CompareSheets_Adv = True
Exit Function
CompareSheetsERR:
CompareSheets_Adv = False
End Function
This code uses a dictionary and arrays to compare both sheets and then displays results on Worksheet "Results". This how it looks like with my dummy entries:
My issue currently is editing my function in a way that I keep the data integrity intact (Identifiers must always match, according to description too when comparing rows) while comparing the function only for the differences(reference of Sheet1="SiteData") . I might have been approaching this wrong from the get go, resorting to asking my question here is a last hope thing for me. I'm not very adept at Excel VBA. Appreciate any and all kinds of help and feedback.
Update: Currently trying to create a hash string identifier for the row and using that to compare the rows regardless of the order, as long as the hash strings match(assuming hash string is the same for two strings).
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|