Vlookup to perform lakhs of data

Help Studio
, , , , , , , , , ,
1

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!

2

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

Regards,
Nived N

3

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

4

Which column u are lookuping here ?
image

5

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.

6

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

7

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

8

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

9

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

11

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

12

Yes, please I shared the xaml file, please help

13

image
image1180×636 35 KB

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

14

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

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
15

image
image1875×504 30.9 KB

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

16

@Anjali_Rani

  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
17

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

18

@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.
1 Like
19

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

20

image
image1887×191 3.55 KB

Hi, while comparing with smaller data, i am getting extra 0;s and other data can you check this once please!!
image
image1831×644 9.51 KB

23

Ok, Sure.Please help me out!