The previous matching code works in the same way, except it is matching USER PREFERENCES OF SITE against SITE PREFERENCES OF USER,
I have tried to modify this but my coding skills are currently at the level to properly code the scripting dictionaries.
I had a previous Unprotect, Protect code in here also which I will keep for this one too.
Option Explicit
Sub Matching_Tool()
Dim a, b, i As Long, ii As Long, n As Long, txt As String, x, dic As Object
Set dic = CreateObject("Scripting.Dictionary")
dic.CompareMode = 1
With Sheets("matching_tool")
a = .ListObjects("table1").DataBodyRange
x = .ListObjects("table1").HeaderRowRange
For ii = 2 To UBound(a, 2)
For i = 1 To UBound(a, 1)
If a(i, ii) <> "" Then
txt = Join(Array(a(i, 1), CStr(a(i, ii))), Chr(2))
dic(txt) = x(1, ii)
End If
Next
Next
ReDim b(1 To dic.Count, 1 To 3)
a = .ListObjects("Table2").DataBodyRange
x = .ListObjects("table2").HeaderRowRange
For ii = 2 To UBound(a, 2)
For i = 1 To UBound(a, 1)
If a(i, ii) <> "" Then
txt = Join(Array(a(i, ii), CStr(a(i, 1))), Chr(2))
If dic.exists(txt) Then
n = n + 1: b(n, 1) = a(i, ii)
b(n, 2) = CStr(a(i, 1)): b(n, 3) = dic(txt)
If dic(txt) <> x(1, ii) Then b(n, 3) = b(n, 3) & "/" & x(1, ii)
End If
End If
Next
Next
'Unprotect a worksheet with a password
Sheets("Matching_Tool").Unprotect Password:="BLANK"
With .ListObjects("table3")
.DataBodyRange.ClearContents
If n > 0 Then
.ListRows(1).Range.Resize(n).Value = b
Else
MsgBox "No matches"
End If
End With
End With
Call Matrix_Rank
'Protect worksheet with a password
Sheets("Matching_Tool").Protect Password:="BLANK"
End Sub
Bookmarks