Vlookup to perform lakhs of data

[NIVED_NAMBIAR (NIVED N)]

Which column u are lookuping here ?

[Anjali_Rani]

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.

[NIVED_NAMBIAR (NIVED N)]

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))

Regards,
Nived N

[Anjali_Rani]

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

[Divyanshu_Divyanshu]

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

[Anjali_Rani]

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

[Anjali_Rani]

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

[Anjali_Rani]

Yes, please I shared the xaml file, please help

[Anjali_Rani]

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

[lojyehuang (Lo-Chieh)]

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

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

[Anjali_Rani]

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

[lojyehuang (Lo-Chieh)]

@Anjali_Rani

Error: Invoke VBA: Programmatic access to Visual Basic Project is not trusted

@CristinaAlina have a Look in this Then Focus on the settings from the Excel Activity e.g Excel Application scope Macros enablement

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.

[Anjali_Rani]

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

[lojyehuang (Lo-Chieh)]

@Anjali_Rani
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.

[Anjali_Rani]

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

[Anjali_Rani]

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

[Anjali_Rani]

Ok, Sure.Please help me out!

[lojyehuang (Lo-Chieh)]

@Anjali_Rani
Change the VBA file
vba.txt (1.4 KB)

[Anjali_Rani]

Thank you so much.