VBA Index & Match vs VLOOKUP

[mps80y]

Hi,

I'm in the process of writing some VBA to match and extract data from one worksheet to another.

Originally I was going down the path of VLOOKUP but have since read about Index & Match being the better way to go.

I've attached a sample of the data here, any suggestions on the best way to accomplish this would be greatly appreciated.

The headers on the 'invoice' tab match the headers for the data required from the 'data' tab, the corresponding columns are also stated in the square brackets. The lookup key is the 'Booking' column.

Any help or ideas are greatly appreciated.

Thanks in advance

 

[alansidman]

Really only need a dozen or so records to provide a solution. To be honest, I am not downloading 48 pages of data as this is an extraneous exercise without much value.

 

[mps80y]

Really only need a dozen or so records to provide a solution. To be honest, I am not downloading 48 pages of data as this is an extraneous exercise without much value.

Good point - I didn't even think about that before uploading it. Sorry. I've stripped most of the data and re-uploaded the file here

 

[alansidman]

I think that the quickest and easiest way to do this is with Power Query. Load both tables to PQ and then do a Query Merge based upon the common field "Booking." Once you have merged the two tables, delete the unused or duplicate columns, keeping only those that are necessary. You can reformat any columns that you need at this point. All done in quick and easy UI steps. No need for code. Sounds like a lot, but in reality will only take a few minutes and will do millions of rows if necessary.

 

[mps80y]

I looked into that originally, but unfortunately it won't be me running the reconciliation each month. I work for a Government department and the process to get something installed on a PC is lengthy and difficult. Training for the user(s) would also be difficult, hence the need to write the code that would do the majority of the work required with a button.

 

[Fluff]

As long as the booking numbers are unique, how about

Sub mps80y()
   Dim Cl As Range
   Dim wsData As Worksheet, wsInvoice As Worksheet
   
   Set wsData = Sheets("Data")
   Set wsInvoice = Sheets("Invoice")
   With CreateObject("scripting.dictionary")
      For Each Cl In wsData.Range("A2", wsData.Range("A" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Application.Index(Cl.Resize(, 23).Value, 1, Array(8, 5, 6, 9, 10, 12, 15, 13, 2, 7))
      Next Cl
      For Each Cl In wsInvoice.Range("A2", wsInvoice.Range("A" & Rows.Count).End(xlUp))
         If .Exists(Cl.Value) Then
            Cl.Offset(, 11).Resize(, 8).Value = .Item(Cl.Value)
            Cl.Offset(, 1).Value = .Item(Cl.Value)(9)
            Cl.Offset(, 9).Value = .Item(Cl.Value)(10)
         End If
      Next Cl
   End With
End Sub

 

[mps80y]

That works perfectly.

Out of curiosity - If it is not able to find a result based on the booking field, would it be possible to then look up the Customer Ref # value in Column C and return the corresponding Booking # from the data sheet in the "Possible match with" Column E?

The reason being that the Booking Number field is manually entered by an operator and sometimes the number is entered incorrectly, but the Customer Ref #'s are sequential numbers and "should" match up (but again thats not always the case, so I'm just trying to build in a "back up" I guess for when there is no result returned.

 

[Fluff]

Difficult to tell as there's no correlation between the customer # & customer ref# in the data you supplied.

 

[alansidman]

Depending on the software version of excel you are running, Power Query/Get and Transform may already be installed on your machine. Secondly, once you have run your merges and saved them, you can set the query to automatically refresh when new data is added to the source documents. While I understand that you will use Fluff's solution, I believe you are cheating yourself and your fellow workers of the latest Excel technology if you don't at least investigate the advantages of Power Query. It is an amazingly powerful addition to the Excel Suite of functions that may save you lots of work in the future. My 2 cents.

 

[mps80y]

Difficult to tell as there's no correlation between the customer # & customer ref# in the data you supplied.

I've fixed the data - sorry about that - I really appreciate your help and input so far