How to vlookup for the huge data

Help Automation Ops
,
1

how to do vlookup if we have huge data and the vlookup data tables are different.How to declare them and how to do vlookup for them.

2

Hello @anjani_priya

Sequence Name: VLookupExample

  1. Assign Activity

    • To: resultTable
    • Value:
      (From row1 In table1.AsEnumerable()
      Join row2 In table2.AsEnumerable() On row1.Field(Of String)(“ID”) Equals row2.Field(Of String)(“ID”)
      Select tableResult.LoadDataRow({row1(“ID”), row1(“OtherColumnFromTable1”), row2(“Value”), row2(“OtherColumnFromTable2”)}, False)).CopyToDataTable()

  2. Log Message

    • Message: “VLOOKUP operation completed.”

Thanks & Cheers!!!

3

I have 3 lakh records it taking to run more than one hour is there any alternate method for vlookup?

4

Hi @anjani_priya

If you want to Vlookup the data in the Excel, there is an activity called Vlookup. It is a excel activity and you have to use inside the Use excel file activity.

Check the below image for better understanding,
image

Or you can use the Macros by using Vb.net code in Invoke vba activity.

Hope it helps!!

5

the vlookup files are different and I dont know macros

6

If you provide the input data and conditions then I’ll give you vb.net code for macros. @anjani_priya

7

SampleCSVFile_2kb .xlsx (23.3 KB)
Book1.xlsx (2.2 MB)
as odn reference the the date and acknowlegement should get from samplecsvfile into book1

8

There is no common reference in Both of the files. @anjani_priya

Min one of the column is same in both of the files, by taking those reference we can Vlookup the data.

Could you be more specific!!

9

ODN is the reference

10

ODN in book1 is

image
image735×298 6.79 KB

But in Sample csv file there is no column called ODN and values are not same in both files.
image

Hope you understand!!

11

If you can see the below example,
In book1 there is a column called Name
image

In book2 there is a column called Student
image

There is common values in both excel files is Names and Student.

If it will be like this then we can take Name and Student as reference in both files and Vlookup the values.

Hope you understand!! @anjani_priya

12

sry i have sent the wrong file

13

Okay @anjani_priya Share the proper files.

14

@anjani_priya

Use INDEX-MATCH: Replace VLOOKUP with INDEX and MATCH.
Sort Data: Ensure your lookup range is sorted.
IFERROR: Wrap your formula with IFERROR for error handling.
Consider Databases: Explore database functions or Power Query.
Split Data: Break down large datasets into smaller chunks.

15

cant we do filter datatable reflection in same excel sheet

16

Book1.xlsx (1.7 MB)
SampleCSVFile_2kb .xlsx (8.5 MB)
as odn reference get the date and acknowledgment from sample to book1

17

@anjani_priya

VLOOKUP is not working for both the files because there is more duplicate rows in the both excel files.

Hope it helps!!

18

@anjani_priya
Find the solution in the attached xaml

Main.xaml (10.9 KB)

Hope it helps!!

19

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