Implementing an efficient way to VLOOKUP a table by trying a LINQ if possible

Help Activities
, ,
1

Hi,

Good Day.

Source.xlsx

An Excel spreadsheet named "Source" displaying a tabular dataset with columns such as date, time, user ID, transaction ID, name, status, and description. (Captioned by AI)
An Excel spreadsheet named "Source" displaying a tabular dataset with columns such as date, time, user ID, transaction ID, name, status, and description. (Captioned by AI)944×504 139 KB

Actual.xlsx

This image shows an Excel spreadsheet with a set of data containing columns for request date, status, and reference number, with entries listed for dates in February 2023. (Captioned by AI)
This image shows an Excel spreadsheet with a set of data containing columns for request date, status, and reference number, with entries listed for dates in February 2023. (Captioned by AI)944×515 84.1 KB

I have this manual process. Shown files above are the Source.xlsx and Actual.xlsx.

First step: Copy the highlighted rows from Source.xlsx where the basis is the current date or today’s date. Then paste the highlighted rows to the Sheet1 of the Actual.xlsx (highlighted in Yellow)

An Excel spreadsheet lists various transaction records, with columns detailing date, time, ID numbers, names, status, and additional descriptive categories. (Captioned by AI)
An Excel spreadsheet lists various transaction records, with columns detailing date, time, ID numbers, names, status, and additional descriptive categories. (Captioned by AI)944×514 152 KB

The image is a screenshot of an Excel spreadsheet showing a table with various transaction details highlighted in yellow, including dates, IDs, account numbers, and statuses. (Captioned by AI)
The image is a screenshot of an Excel spreadsheet showing a table with various transaction details highlighted in yellow, including dates, IDs, account numbers, and statuses. (Captioned by AI)801×696 119 KB

Second Step: In the Actual sheet of Actual.xlsx, apply this VLookUp Formula: =VLOOKUP(@C:C,Sheet1!E:F,2,0) in the column beside the Reference Number (highlighted in green)

The image shows an Excel sheet titled "Actual" with a VLOOKUP formula being used to find corresponding values for a list of reference numbers, resulting in a column of green-highlighted cells. (Captioned by AI)
The image shows an Excel sheet titled "Actual" with a VLOOKUP formula being used to find corresponding values for a list of reference numbers, resulting in a column of green-highlighted cells. (Captioned by AI)944×1008 165 KB

May I ask for help to know what is the most efficient way to automate this one even if it is composed of thousands of rows? Can we implement LinQ instead of VLookUp formula and generate the same output efficiently?

Kindly see attached files for Source.xlsx and Actual.xlsx. Thank you.

Actual.xlsx (10.5 KB)
Source.xlsx (10.2 KB)

Best regards,
Llessur

2

Hi @Llessur

If you use excel activities it will take some more time than linq queries when you have a large number of data.

You can use linq queries but it will take sometime to write the datatable back to excel.

Select the approach based on your requirement and data size.

Hope it helps!!

1 Like
3

Hi @Llessur

Can you try the below

This image is a workflow diagram, likely created in UiPath Studio, illustrating the steps to read data from two Excel workbooks, add a column, process the data with custom code, and then write the updated data back to an Excel workbook. (Captioned by AI)
This image is a workflow diagram, likely created in UiPath Studio, illustrating the steps to read data from two Excel workbooks, add a column, process the data with custom code, and then write the updated data back to an Excel workbook. (Captioned by AI)1147×820 54.3 KB

Code:

For Each row1 As DataRow In dt_Actual.Rows
    Dim ReferenceNumber As String = row1("Reference Number").ToString()
    Dim foundRow As DataRow = dt_Source.AsEnumerable().FirstOrDefault(Function(r) r(4).ToString() = ReferenceNumber)
    If foundRow IsNot Nothing Then
        row1("New Col") = foundRow(5)
    End If
Next

Output:

The image shows a spreadsheet with columns labeled Request Date, Status, Reference Number, and New Col, containing data for February 26, 2025, and all entries marked as "BATCHED" in the Status column. (Captioned by AI)
The image shows a spreadsheet with columns labeled Request Date, Status, Reference Number, and New Col, containing data for February 26, 2025, and all entries marked as "BATCHED" in the Status column. (Captioned by AI)722×337 7.93 KB

Sequence4.xaml (11.5 KB)

Note: Uncheck Add Headers option in Source DataTable i.e., dt_Source

Regards,

1 Like
Forum Engagement Daily Reports
4

Hi @lrtetala the provided .xaml file writes the output in the Sheet1 instead of writing it in the Actual sheet

5

Hi @Llessur

You can change the value of the Path and Sheet Name in the Write Range Workbook as per your requirement in the solution given by @lrtetala

1 Like
6

I the write range workbook activity, give Active instead of Sheet 1 in sheet name field… @Llessur

Hope it helps!!

7

Hi @V_Roboto_V @lrtetala @mkankatala thank you so much for your help. I’ve unchecked the Add Headers in the Read Range for Source.xlsx and Write Range for Actual.xlsx. Thank you.

1 Like
8

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