Improve Vlook up in large dataset

[malle7]

Hi Everyone,


I have 2 worksheets

Sheet: "UniqueCodesInfo" with distinct values in column B (Output)
Sheet: "Sheet1" with a large data (Source Sheet)


What I am trying to do...
type VlookUp function in Cell D2- Sheet "UniqueCodesInfo"...Searching in Sheet1 column B
type VlookUp function in Cell E2- Sheet "UniqueCodesInfo"...Searching in Sheet1 column C
type VlookUp function in Cell F2- Sheet "UniqueCodesInfo"...Searching in Sheet1 column G......

And so on until Column W UniqueCodesInfo.


The code below is only for 1 column because Code is Exremely slow. and I run it one at the time.

I would really appreciate your support with this.



----------------------------------------------------------------------------

Sub VlookUp_D()

Application.ScreenUpdating = False

Dim SourceLastRow As Long
Dim OutputLastRow As Long
Dim sourceSheet As Worksheet
Dim outputSheet As Worksheet



'What are the names of our worksheets?

Set sourceSheet = Worksheets("Sheet1")
Set outputSheet = Worksheets("UniqueCodesInfo")



'Determine last row of source
With sourceSheet
SourceLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With

With outputSheet

'Determine last row in col B
OutputLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

'Apply our formula

.Range("D2:D" & OutputLastRow).Formula = "=VLOOKUP(b2,'" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0)"

End With

Range("D2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'ActiveWorkbook.Save

Application.ScreenUpdating = True

End Sub

 

[Anthony47]

You can dramatically improve vlookup performance by sorting the table in Sheet1. At that point you will be able to use vlookup in the "approximate" mode, using a formula with the following structure

=if(vlookup(TheKey,TheTable,1,1)=TheKey,vlookup(TheKey,TheTable,2,1),NA())

If you have to extract from the searched table the full row, then probably Index & Match should perform better, because you can extract with a single instruction the whole row;

=INDEX(TheDataColumn,MATCH(TheKey,TheKeyColumn),0)

0=Return the whole row

And of course also Match can benefit by sorting TheTable

If you use Office 365 then you should use XLookup instead of VLookup
You should also consider if populating UniqueCodesInfo with Values rather then Formulas

For further details you should share a sample workbook, and should also specify what you mean for "large dataset": 10000 rows or 1million?

Bye

 

[alansidman]

An alternative to Vlookup, you can merge your two tables in Power Query. Watch this video and see how quickly this works and no coding required.