Vlookup to perform lakhs of data

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!

Change the VBA file
vba.txt (1.4 KB)

1 Like

Thank you so much.

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.