Match function vba

[wilsonwilson4]

Hi All,

Sorry i am new in mr.excel. I am wondering if there is anyway to replace "&" in Match formula and use range instead?

I have a large data set that need to compare and found if there is any different. I found that using match is very useful but it will be painful if there are many columns to be compared.

=Match(A2&B2&C2, $H$2:$H$4&$I$2:$I$4&$J$2:$J$4,0)


For example
Data Set 1 A - C column

Data 1	Data 2	Data 3
123​	A	D
222​	B	E
333​	C	F

Data Set 2 H-J Column
In this case Data 2 - 222 - will have N/A or not match with data set 1

Data 1	Data 2	Data 3
123​	A	D
222​	B	D
333​	C	F

 

[Joe4]

Quite frankly, I find this sort of task much easier to do in Microsoft Access where you can do Matched or Unmatched Queries based on multiple columns.

Note that Excel now offers "Power Query", which allows you to do many database-type actions (like queries like these) in Excel.
Since I am partial to Microsoft Access, I have not used Power Query myself, but there are many people here who do.
There is even a forum here for asking question about the various Power Tools: Power Tools

 

[wilsonwilson4]

Thanks for the replay.. may I know what query u use in access? It seems only allow 1 column with query wizard unmatched.

 

[offthelip]

you can do that very efficiently using the the VBA dictionary object. like this:

Sub test()
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Ary = .Range(.Cells(1, 1), .Cells(LastRow, 3))
   Dim Dic As Object
   Set Dic = CreateObject("Scripting.dictionary")
      
   For i = 2 To UBound(Ary, 1)
        ' concatenate 3 columns
         tt = ""
         For j = 1 To 3
         tt = tt & Ary(i, j)
         Next j
         Dic(tt) = i
   Next i
End With

With Worksheets("Sheet2")
LastRow = .Cells(Rows.Count, "H").End(xlUp).Row
datar = .Range(.Cells(1, 8), .Cells(LastRow, 10))
outarr = .Range(.Cells(1, 7), .Cells(LastRow, 7))
For i = 2 To LastRow
         tt = ""
         For j = 1 To 3
         tt = tt & datar(i, j)
         Next j
         If Dic.Exists(tt) Then
         outarr(i, 1) = Dic(tt)
         Else
         outarr(i, 1) = "Not Found"
         End If
Next i
 .Range(.Cells(1, 7), .Cells(LastRow, 7)) = outarr
End With
End Sub

 

[Joe4]

Thanks for the replay.. may I know what query u use in access? It seems only allow 1 column with query wizard unmatched.

That is only if you use the Query Wizard (which, you do not have to do, that is only a tool to help people along).
You can actually start out with the Unmatched Query Wizard (which only joins on one field).
Then, when you finish setting it up, you can edit the query to join on the other fields to.

Any basic/introductory book/video/tutorial on creating queries in Access will show you how to join on multiple fields in a query (it is a very common thing to do).

This tutorial shows you how to do an Unmatched Queries when joining on multiple fields (see the "Create and modify a Find Unmatched Query to compare by more than one field" section):

Compare two tables and find records without matches

Use the Find Unmatched Query in Access to compare two tables and identify records in one table that don't have corresponding records in the other table.

support.microsoft.com