Filter and match 2 data tables with multiple columns

Help Studio
, , ,
1

Hi Guys,

I have 2 data tables DT1 and DT2 and my goal is to match address from these 2 excel files (data tables).

Logic need to be build in this order:

Filter DT1 with COUNTRY = USA → Copy EID (one by one) and search it in DT2 (Employee ID) → If found → Check if Country from DT1 (“USA”) = COUNTRY from DT2 (“United States”).
If matches, then pick POSTAL code from DT1 and search it in DT2 of Postal Code → Likewise, next drill down for STATE, CITY, ADDRESS1, ADDRESS2, ADDRESS3 matching from DT2.

Sample data: DT1 → I have below columns:

image
image689×124 4.49 KB

And, Sample data: DT2 → Few column names are different for same meanings for ex. “Region 2” = “STATE” in DT1 etc..):

image
image908×110 5.02 KB

If anyone can build this logic, that would be great.

BR,
NK

2

Hi, @Nitesh you can try this logic

Filter DT1 for rows with Country = “USA”.

For each Employee ID in DT1, find the matching EID in DT2.

if found, compare Country in DT1 (“United States”) with Country in DT2 (“USA”) using .ToUpper.Trim for both.

Check Postal Code, State/Region, City, Address1, Address2, and Address3 match with their respective columns (map Region 2 <=> STATE).

Continue for all rows in DT1, and collect matches to use as needed.

3

Hi @arjun.shiroya ,

Thanks for your reply. I have already applied this logic - for each loop.

My logic:

For each row in DT1:
CurrentRow in DT1
For each row in DT2:
CurrentRow2 in DT2
IF:
CurrentRow(“EID”).ToString = CurrentRow2(“Employee ID”).ToString
and then nested if’s, I have applied.

And also, if it doesn’t matches the address I need to store them all unmatched addresses in 1 excel.

BR,
NK

4

Use this LINQ (returns all perfectly matched DT1 rows as a DataTable):

Dim matched = (From dt1row In DT1.AsEnumerable()
               Where dt1row("COUNTRY").ToString.Trim.ToUpper = "USA"
               Join dt2row In DT2.AsEnumerable()
               On dt1row("EID").ToString.Trim Equals dt2row("Employee ID").ToString.Trim
               Where dt2row("Country").ToString.Trim.ToUpper = "UNITED STATES" AndAlso
                     dt1row("POSTAL").ToString.Trim = dt2row("Postal Code").ToString.Trim AndAlso
                     dt1row("STATE").ToString.Trim = dt2row("Region 2").ToString.Trim AndAlso
                     dt1row("CITY").ToString.Trim = dt2row("City").ToString.Trim AndAlso
                     dt1row("ADDRESS1").ToString.Trim = dt2row("Address Line 1").ToString.Trim AndAlso
                     dt1row("ADDRESS2").ToString.Trim = dt2row("Address Line 2").ToString.Trim AndAlso
                     dt1row("ADDRESS3").ToString.Trim = dt2row("Address Line 3").ToString.Trim
               Select dt1row).CopyToDataTable()
  1. For Unmatched Rows:
    Use .Except to get all DT1 rows (USA filtered) that are not in matched
Dim dt1USA = DT1.AsEnumerable().Where(Function(r) r("COUNTRY").ToString.Trim.ToUpper = "USA")
Dim unmatched = dt1USA.Except(matched.AsEnumerable(), DataRowComparer.Default).CopyToDataTable()

Export: Use UiPath “Write Range” to write matched and unmatched DataTables to separate Excel sheets.

Formated by AI

1 Like
5

Thanks alot @arjun.shiroya

I got it for matched rows, but I am facing error for unmatched row.

Is it possible if we can write code for unmatched row and store in a single variable just like for matched row?

BR,
NK

6

@Nitesh You’re very welcome!

Yes, you can store unmatched rows in single DataTable variable jst like matched rows by using LINQs except method:

Dim dt1USA = DT1.AsEnumerable().Where(Function(r) r("COUNTRY").ToString.Trim.ToUpper = "USA")
Dim unmatched = dt1USA.Except(matched.AsEnumerable(), DataRowComparer.Default)
If unmatched.Any Then
    Dim unmatchedDT = unmatched.CopyToDataTable()
Else
    Dim unmatchedDT = DT1.Clone() 'returns an empty table with headers if no unmatched rows
End If

This gives you all unmatched rows in one variable unmatchedDT

7

Thanks alot @arjun.shiroya
It worked.

Below is the updated code which worked:

’ Get only USA rows, always returns a DataTable (even if no matches)

dt1USA =
If(DT1.AsEnumerable.Where(Function(r) r(“COUNTRY”).ToString.Trim.ToUpper = “USA”).Any,
DT1.AsEnumerable.Where(Function(r) r(“COUNTRY”).ToString.Trim.ToUpper = “USA”).CopyToDataTable,
DT1.Clone)

’ Get unmatched, always as a DataTable (even if empty)

unmatchedDT = If(dt1USA.AsEnumerable.Except(matched.AsEnumerable, DataRowComparer.Default).Any,
dt1USA.AsEnumerable.Except(matched.AsEnumerable, DataRowComparer.Default).CopyToDataTable,
DT1.Clone)

Thanks alot for helping me with this code.

BR,
NK

8

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