Vlookup to perform lakhs of data

Hi everyone,
I have two excels with more tan lakhs of data and i have to perform vlookups on first dt, and if common value found then replace their price with dt2 value.
I tries this solution but this is not working.
Please find my solution, it is taking more than 4hrs to process lakhs of data… Even i am not able to attach that excel , the file size is big.
Test.xaml (14.3 KB)

Please help!

Hi @Anjali_Rani

let’s assume the following :

dt1 datatable where u are taking the common value
dt2 u are doing the lookup
c1 —> common column name in both datatables.
price ----> column name for price in dt1

Then try this code in invoke code

dt1.AsEnumerable().ToList().ForEach(Sub(row) row(“price”)= if(dt2.AsEnumerable().Where(Function(r) r(“c1”).ToString.Trim.Equals(row(“c1”).ToString.Trim).Count<>0, dt2.AsEnumerable().Where(Function(r) r(“c1”).ToString.Trim.Equals(row(“c1”).ToString.Trim)(0)(“price”).ToString,row(“price”).ToString))

where dt1, dt2 should be passed as in/out argument to invoke code

Nived N

Thank you. Can you please modify this on my xaml file . I am not getting properly how to apply.

Which column u are lookuping here ?

I have to update this table, this table have also lakhs of record, and in this , stock level and buynowprice needs to update if sku will match in both the files.

Then use this code in invoke code

argument —>
dt1 ---- In/Out
dt2 ----- In/Out

dt1.AsEnumerable().ToList().ForEach(Sub(row) row(“price”)= if(dt2.AsEnumerable().Where(Function(r) r(“sku”).ToString.Trim.Equals(row(“sku”).ToString.Trim).Count<>0, dt2.AsEnumerable().Where(Function(r) r(“sku”).ToString.Trim.Equals(row("sku”).ToString.Trim)(0)(“price”).ToString,row(“price”).ToString))

dt1.AsEnumerable().ToList().ForEach(Sub(row) row(“quantity”)= if(dt2.AsEnumerable().Where(Function(r) r(“sku”).ToString.Trim.Equals(row(“sku”).ToString.Trim).Count<>0, dt2.AsEnumerable().Where(Function(r) r(“sku”).ToString.Trim.Equals(row("sku”).ToString.Trim)(0)(“price”).ToString,row(“quantity”).ToString))

Nived N

getting error. Can you please append this part on my soln.I never used Invoke code.
Please help.

Can you share the xaml ??
Will correct and send it back

Test (1).xaml (16.9 KB)
Hi,Please find

Hi, please check my my xaml file soln once ,pleas help

Yes, please I shared the xaml file, please help

Hi,Please help me to solve this error! i am not sure why this error is coming.
Please help.

Hi @Anjali_Rani
How about invoke VBA?
Execution time only 3~4mins.

Here is my solution.
Forum1215.zip (8.6 MB)

Sub TEST()

    Dim RowsCountSheet1 As String
    Dim RowsCountData As String
    RowsCountSheet1 = Sheets("Sheet1").UsedRange.Rows.Count
    RowsCountData = Sheets("Data").UsedRange.Rows.Count

    ActiveCell.FormulaR1C1 = _
        "=IFNA(VLOOKUP(RC[-27],Sheet1!R1C1:R" & RowsCountSheet1 & "C4,2,FALSE),RC[-26])"
    ActiveCell.FormulaR1C1 = _
        "=IFNA(VLOOKUP(RC[-28],Sheet1!R1C1:R" & RowsCountSheet1 & "C4,3,FALSE),RC[-26])"
    Selection.AutoFill Destination:=Range("AC2:AD" & RowsCountData & "")
    Range("AC2:AD" & RowsCountData & "").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.Delete Shift:=xlToLeft
End Sub

Hi thank you very much for help, but i am getting this error. Can you please help me out!


  1. Open a blank workbook on MS Excel
  2. Got to File >> Options
  3. Click on Trust Center >> Trust Center Settings
  4. Click on Macro Settings >> Check ‘Trust Access to the VBA project object model’ (under Developer Macro Settings)
  5. Restart your Excel.
1 Like

Hi can you please help me to understand this code? Please can you explain little bit

Does this workflow work fine?
This code meaning is:

  1. Copy dt2 to dt1 in order to Vlookup
  2. Call VBA to vlookup , search the new price and quantity in dt2(Sheet1)
    and show them in Column AC and AD.
  3. If there is no new price and quantity, it will show NA. Then IFNA will show the original price and quantity.
  4. Copy the Column AC:AD to D:E to replace the old price and quantity.
1 Like

Yes, working Thank you so much!! Thanks a ton. :slight_smile:

Hi, while comparing with smaller data, i am getting extra 0;s and other data can you check this once please!!

Ok, Sure.Please help me out!