Comparison with two dynamic variables

Hi,

I have a list of vendors names in dt1, and I need to compare each row of company names in dt2 against each row from dt1. If they match, do nothing, if they do not match, add data row to new dt3

image

image

I have tried running through a for each loop and IF condition: var1.Equals(var2) then nothing, else add data row.

This is not working as I intend it to. If I run the for each row in dt2 against the Vendor list, it does not find a match, even though you can clearly see there is. If I run the opposite for each row in dt1 against the Company List then it finds the match, but with the IF condition it adds a data row for each of the vendors not matched.

I have been trying anything I can think of, but I am struggling. Does anyone know what I am doing wrong? Thank you in advance!

For each row(dt) >> if Condition(dt1.AsEnumerable.Count(Function (x) x.Item(“Company Name”).ToString = CurrentRow.item(“Vendor”).ToString) > = 1)
dt is First image(Vendor Column)
dt1 is second image(Company Name Column)

I wouldn’t necessary recommend For Each Row approach especially if you are dealing with large transactions row but if you are dealing with small transaction I guess it’s just fine.

You can try this LINQ approach.

Assign to dt3 =

(
From vendor In dt1
Join company In dt2
On vendor("Vendor").ToString.Trim Equals company("Company Name").ToString.Trim
	Let MatchedColumn = vendor("Vendor").ToString
Select dt3.Rows.Add(MatchedColumn)	
).CopyToDataTable

Here is the sample process xaml:
Comparison with Two Dynamic Variables.xaml (17.3 KB)

1 Like

Hi @chrystine.h

Try this:

1. Read 'dt1' and 'dt2' using appropriate activities.
2. Create 'dt3' with the same structure as 'dt2'.
3. Use an Assign activity to filter and copy unmatched rows from 'dt2' to 'dt3' using LINQ:
   Assign activity:
       dt3 = (From rowDt2 In dt2.AsEnumerable()
          Where Not dt1.AsEnumerable().Any(Function(rowDt1) rowDt1.Field(Of String)("VendorName") = rowDt2.Field(Of String)("CompanyName"))
          Select rowDt2).CopyToDataTable()
4. Use 'Write Range' activity to output 'dt3' to a new file or sheet.

Hope it helps!!

2 Likes

Thank you to everyone who tried to help me, I do appreciate it! A special thanks to @Parvathy for giving me the easiest solution EVER wow. Have a great day :smile:

1 Like

Thank you @ton Your solution is just as easy too!

You’re welcome @chrystine.h.

Happy Automation
Regards,

1 Like

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