Match Excel cells to get information from another

Hi all, I’m trying to retrieve an email from an excel, but I would need to match the Name and Company first. Here are my steps of the workflow:

  1. Output Data Table of Excel A and Excel B
  2. Excel A has Name, Company, Date // Excel B has Name, Company, Email
  3. I have managed to filter Excel A date and get the Name and Company identified from that specific date.
    *4. Now I’m confused at this step where I have to get the Email from Excel B, but it has to match both Name and Company of Excel A

I have added some sample examples in case it gets too confusing,

For example I have to extract Name and Company with “01/07/2022” as date from excel A.
and match that Name and company in excel B and get their email stated in excel B.

What are the steps I should take to implement this? Thanks in advance.

Excel A (Left) / Excel B (Right):

Hi @standbones,

Welcome to the community.

You can use Join function for this. UiPath has a join activity you can refer that or a LINQ query can be written for joining the two datatables and storing the result you want into a third one.

You can refer below post.

Thanks

1 Like

Hi @Shikhar_Tandon ,

Appreciate the prompt response and beneficial help. There are still some obstacles, but I believe we’re on the right track.

I’ve implemented the join activity (left join), and the sample result is as shown below.
The current issue now is, based on the blue table, I am supposed to get Irene’s email.

However, in the orange table, I have 2 contact emails in the company (Irenes Bakery Shop):

  1. Irene
  2. Karina

I am only supposed to get Irene’s email, how do I implement this step to retrieve the email of the specified Name in Column ‘Name_1’ , based on the Company Name?

Note that the actual data we have people with same names, so we need to additionally cross check the ‘Company’ together with the ‘Name’.

Thanks in advance!

You could do that using the Filter Data Table activity.

You would configure it something like this:

The input data table should be the one containing your email addresses. The results would be stored in the output data table. Replace the hardcoded name and company with your own variables.

You could add handling for != 1 match, but if the filtered results do contain 1 row, then there was a match and you could access the email value like so.
dtFilteredResults.Rows(0).Item("Email").ToString

This article also shows some other ways of filtering.

Hey! Welcome to community!

Try like this:

  1. Read first excel->DT_1
  2. Read second excel->DT_2

Take one for each row pass the first DT_1
Take one more For each roe pass the second DT_2

Take one IF Condition mention the Condition like this

CurrentRow1("Name").ToString.Trim.Equals(CurrentRow2("Name").ToString.Trim) AND CurrentRow1("Company").ToString.Trim.Equals(CurrentRow2("Company").ToString.Trim)

Then:

Take one Assign Activity->Create one String Variable

Assign Email = CurrentRow2("Email").ToString.Trim

Else:

Take one Continue Activity

Try this and let me know

Regards,
NaNi

1 Like

Hi @THIRU_NANI ,

Thank you, this worked wonderfully!

However, in the actual DT, I have thousands of rows in DT2 (which contains the email) as it is like a contact list. The bot is crawling through each data searching for the correct “Name” and “Company” to pull the email and that is taking hours to process.

Essentially what I’m trying to do in this whole process is to retrieve the “Email” of the persons “Name” at a “Company” at a specific “Date”.

I have managed to filter the specific date I want and output the result into another filteredDT_1, so it only shows Companies and Names which has the specified date tagged to them. This is what it looks like when I filter to date “14-11-22”

DT1
image

filteredDT_1
image

I was wondering if I can use the “Company” listed in filteredDT_1 's and straightaway locate the “Company” in DT_2 and pull the “Email” out? Rather than crawling through all the data to match.

Thanks!

Hey!

You can achieve this… But earlier you mentioned that you’re dealing huge no.of data…

For each will take a lot of time…

You can achieve this by using LINQ.

To be frank I am not familiar with LINQ

Regards,
NaNi

Hi @standbones ,

When you had implemented the Join Activity, what was the configuration done ?

Could you provide us a Screenshot ?

I believe you would need to use two conditions :
image

Let us know if you have already implemented this.

Hi @supermanPunch ,

Yes I’ve tried the join activity. Below is my config and result when I write range for the output DT.
image

Output:

It’s odd that the other columns are left blank when I include “Company” in my conditions.
Did I miss something out?

Thanks!

Hi Bro.

Try this one.
JoinDatatable.zip (69.1 KB)

1 Like

Hi @Mr.H

Thanks for the file, I’ve tried your method but the email is not shown on the output. I’ve attached the screenshot & my zip file below.

image

test.zip (26.6 KB)

Would you be able to identify the issue? I can’t seem to locate why the email is not showing.

Thanks!

1 Like

Hi Bro.

I think you have something not correct in filter activity.

I have corrected it as attachment.

test.zip (35.6 KB)
image

1 Like

Thank you so much, this solved it!
Careless mistake on my end

1 Like

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