• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Alternate for vLookUp

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi,

One of my friend is facing this issue. He is applying vLookUp in his code.
There are approximately 40,000 rows in the worksheet he is applying formula.
Issue is, vLookUp is taking 10-20 minutes to run via code.

Is there any alternate to vLookUp.

Really sorry I do not have data with me, but I have confirmed that the data format is suitable for applying vLookUp.

Two base columns and one output column to be fetched.

Only unconfirmed aspect is, where data is in different files or different worksheet of same file.

Can anyone please help me in this.
 
ThrottleWorks said:
Hi,

One of my friend is facing this issue. He is applying vLookUp in his code.
There are approximately 40,000 rows in the worksheet he is applying formula.
Issue is, vLookUp is taking 10-20 minutes to run via code.

Is there any alternate to vLookUp.

Really sorry I do not have data with me, but I have confirmed that the data format is suitable for applying vLookUp.

Two base columns and one output column to be fetched.

Only unconfirmed aspect is, where data is in different files or different worksheet of same file.

Can anyone please help me in this.
Click to expand...
You can try below code by adjusting workbook, sheet and range references to suit.
Code: 
Public Sub VBAVLookup()
Dim wbkSource As Workbook, wbkDestn As Workbook
Dim wksSource As Worksheet, wksDestn As Worksheet
Dim varSrcData As Variant, varDstData As Variant
Dim i As Long
'\\ Set references here
Set wbkSource = Workbooks("Book2"): Set wksSource = wbkSource.Sheets("Sheet1")
Set wbkDestn = Workbooks("Book1"): Set wksDestn = wbkDestn.Sheets("Sheet1")
varSrcData = wksSource.Range("A2:B" & wksSource.Range("A" & Rows.Count).End(xlUp).Row).Value
varDstData = wksDestn.Range("A2:B" & wksDestn.Range("A" & Rows.Count).End(xlUp).Row).Value
'\\ Process
With CreateObject("Scripting.Dictionary")
    '\\ Get Data
    .Comparemode = vbTextCompare '\\ Change it if you need case sensitive
    For i = LBound(varSrcData) To UBound(varSrcData)
        If Not .Exists(varSrcData(i, 1)) Then
            .Add varSrcData(i, 1), varSrcData(i, 2)
        End If
    Next i
    '\\ Populate destination data
    For i = LBound(varDstData) To UBound(varDstData)
        If .Exists(varDstData(i, 1)) Then
            varDstData(i, 2) = .Item(varDstData(i, 1))
        End If
    Next i
    '\\ Load back
    wksDestn.Range("A2:B" & wksDestn.Range("A" & Rows.Count).End(xlUp).Row).Value = varDstData
End With
End Sub

BTW, you can ask your friend to join Chandoo.org forums assuming he/she is not shy to ask questions directly ;)
 
Hi @shrivallabha , thanks a lot, I have given this link to my friend. Hope he joins the Forum. We will revert with details. Have a nice day ahead. :)

Hi @Asheesh sir, thanks a lot, am going through these links. Have a nice day ahead. :)
 
Hi Shrivallabha,

Above code worked perfectly but its only pulling data for one column. I have 13 columns to vlookup. Do you have revised code for that?

Thanks
 
Dokat said:
Hi Shrivallabha,

Above code worked perfectly but its only pulling data for one column. I have 13 columns to vlookup. Do you have revised code for that?

Thanks
Click to expand...
You will have to provide example file which identifies criteria and 13 columns clearly.
 
Good Morning,

Please see attached files. i am trying to lookup "segmentation"file using primary key in column 'D" IN FINAL workbook. I wrote the vlookup formula in the area thats highlighted in blue. Hope this helps. Thanks
 

Attachments

  • Final.xlsm
    38.8 KB · Views: 1
  • Segmentation1.xlsx
    22.8 KB · Views: 0
You must log in or register to reply here.
Back
Top