• 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.

VBA code to vlookup between workbooks

D

Dokat

Member
Hi

I have two workbooks with very large tables. One of them has 12 columns and 280K rows. The other ones has 12k rows and 33 columns. I am using vlookup to look for matching values in large table to small one. Vlookups take forever to calculate. Is there an easy way to do this with a VBA code? Can someone share a sample code for me to replicate?
Workbook to vlookup "Source" Sheet Name: "All Data" (Large Table)
Workbook to vlookup from "Segmentation" Sheet Name: 2016 (Small Table)


Thanks
 
Hi:

This is frequently asked question here, please give a search to get the results.

Thanks
 
Hi Nebu,

i found the code to do vlookup for one column in shirvallabah post in the forums...however it only works for one column and i have 13 to vlookup.

Thanks
 
Its many to many relationship thus MS Query creates duplicates when i try to merge it. I tried below code Shrivallabha posted and it worked perfectly however my issue is its only doing vlookup for one column. I ahve 13 columns to vlookup. Thanks
Code: 
PublicSub VBAVLookup()
Dim wbkSource As Workbook, wbkDestn As Workbook
Dim wksSource As Worksheet, wksDestn As Worksheet
Dim varSrcData AsVariant, varDstData AsVariant
Dim i AsLong
'\\ Set references hereSet 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
'\\ ProcessWithCreateObject("Scripting.Dictionary")
   '\\ Get Data   .Comparemode = vbTextCompare '\\ Change it if you need case sensitive   For i = LBound(varSrcData) ToUBound(varSrcData)
       IfNot .Exists(varSrcData(i, 1)) Then
            .Add varSrcData(i, 1), varSrcData(i, 2)
       EndIf
   Next i
   '\\ Populate destination data   For i = LBound(varDstData) ToUBound(varDstData)
       If .Exists(varDstData(i, 1)) Then
            varDstData(i, 2) = .Item(varDstData(i, 1))
       EndIf
   Next i
   '\\ Load back   wksDestn.Range("A2:B" & wksDestn.Range("A" & Rows.Count).End(xlUp).Row).Value = varDstData
EndWith
EndSub
 
Last edited:
Many to many can be dealt with using various methods in SQL (such as windowed function etc).

In any case, you will need to modify Shrivallabha's code significantly. There are several ways to go about it. Loop each column, join/concatenate columns etc.

But to get better help, you'll need to upload sample workbooks containing your data structure (matching real scenario closely as possible).
 
Hi Chihiro,

Attached you will find the two workbooks i am trying to do vlookups via vba code. Original file has over 280K rows that you may understand its taking too long for vlookups to calculate. I am trying to pull data for area highlighted in blue from "Segmentation" workbook to "Final" Workbook.Primary Sku is the unique key...but as you see there are many instances same primary key repeats more than multiple times in each workbook

I will appreciate any help you can give. Thanks
 

Attachments

  • Final.xlsx
    14.4 KB · Views: 24
  • Segmentation.xlsx
    103.3 KB · Views: 31
What would be the desired output when there's multiple match for Primary SKU?

For an example, 00928801. Which of 3 matches is the correct output (Row 155, 189 or 194)? What logic is used to decide which one?
 
Please see attached file. I included the desired output in the final workbook in the area highlighted in blue. Either 155, 189 or 194 is correct. All i am trying to do is pull the segmentation from those rows. They all have the same segmentation thus any of those rows will return same results. I hope this helps . Thanks
 

Attachments

  • Final.xlsx
    65.7 KB · Views: 35
  • Segmentation.xlsx
    104.2 KB · Views: 49
Hi Nebu,

Thank you for taking time to write the code. However when i ran it i receive below error message. Can you please help. Thank you

upload_2016-11-21_19-32-11.png
 
Hi:

I think you have not saved both the workbook in the same folder, The code Thisworkbook.path will give the path where your macro workbook is saved.

Thanks
 
Hi:

There are ways to speed up the code by removing the loops. But I will have to rewrite the code in a different way.

Thanks
 
Hi Nebu,

Iam testing the query and it'sy been running for 27 mins and excel is not responding. If you are going to rewrite the query is there a for it to allow saving files under seperate path?

Thanks
 
Hi Nebu,

Thank you for sending me the updated code. When i ran it opens up segmentation file and gives me below error message. Did you come accross the same issue? Thanks

upload_2016-11-21_22-3-31.png
 

Attachments

  • upload_2016-11-21_22-2-32.png
    upload_2016-11-21_22-2-32.png
    24.8 KB · Views: 6
Yes it is. I changed range and still not sure whats causing the error message. Thanks


Code: 
Sub test()
Application.ScreenUpdating = False

Dim sw As Workbook
Dim dw As Workbook
Dim srng As Range

swname$ = "Segmentation.xlsx"
swpath$ = ThisWorkbook.Path & "\" & swname
Set dw = ThisWorkbook
c& = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
Workbooks.Open (swpath)
Set sw = Workbooks(swname)
Set srng = sw.Sheets(1).Range("D:R")

    Sheet1.Range("T2:T" & c).Formula = "=VLookup(D2,[Segmentation.xlsx]Sheet1!" & srng.Address & ", 5, False)"
    Sheet1.Range("U2:U" & c).Formula = "=VLookup(D2,[Segmentation.xlsx]Sheet1!" & srng.Address & ", 8, False)"
    Sheet1.Range("V2:V" & c).Formula = "=VLookup(D2,[Segmentation.xlsx]Sheet1!" & srng.Address & ", 9, False)"
    Sheet1.Range("W2:W" & c).Formula = "=VLookup(D2,[Segmentation.xlsx]Sheet1!" & srng.Address & ", 10, False)"
    Sheet1.Range("X2:X" & c).Formula = "=VLookup(D2,[Segmentation.xlsx]Sheet1!" & srng.Address & ", 11, False)"
    Sheet1.Range("Y2:Y" & c).Formula = "=VLookup(D2,[Segmentation.xlsx]Sheet1!" & srng.Address & ", 13, False)"
    Sheet1.Range("Z2:Z" & c).Formula = "=VLookup(D2,[Segmentation.xlsx]Sheet1!" & srng.Address & ", 14, False)"
    Sheet1.Range("AA2:AA" & c).Formula = "=VLookup(D2,[Segmentation.xlsx]Sheet1!" & srng.Address & ", 15, False)"
   
    Sheet1.Range("T2:AA" & c).Copy
    Sheet1.Range("T2").PasteSpecial xlValues
    Application.CutCopyMode = False
sw.Close

Application.ScreenUpdating = True
End Sub
 
Hi:

I am not sure about the error the macro is running just fine at my end. Can you highlight the line where you are getting the error.

Thanks
 
It highlights " Sheet1.Range("T2:T" & c).Formula = "=VLookup(D2,[Segmentation.xlsx]Sheet1!" & srng.Address & ", 5, False)"" in yellow

upload_2016-11-21_22-22-10.png
 
Hi:

I think the cell references in your vlookup formula will be wrong since you are having different columns when compared to your uploaded file. Check for the cell references in your Vlookup formula.

Thanks
 
I added 2 additional columns to the original "Final" file thats why i changed the reference columns. There are no changes in"Segmentation" file. However when i try to run the code the file you sent i still get the same error. Can you run without any issue on your end? Thanks
 
You must log in or register to reply here.
Back
Top