Compare Two Data Tables Then Pick Specific Cell Value

Help
, ,
1

Hello Forum,
I am new to uipath , i am trying to automate use case, i have two excel files
macrosheet.xlsx
data.xlsx

image
image888Ă—102 2.52 KB
Data.xlsx

For Each row in Data.xlsx
i need to check for all the given data in the Macro Sheet file.
If payment mode, bank name, and RRN number is a Matched then pick up the macro
name and dump it into the Data file in Column E.

Can anyone help me with this !!

Thank You in Advance

2

@Palaniyappan @NIVED_NAMBIAR

3

@Vajrang

4

Assumption
data is DataTable for data excel file
macro is DataTable for macrosheet excel file
column headers are “Sr.No, PaymentMode, Bank Name, RRN, Macro Name, Number of Matches”.

  1. read excel file and make datatable → data, macro
  2. foreach in data DataTable
    for each rows, find macro row using by FirstOrDefault Method
    FirstOrDefault Method return first finded row or null(if no element is founded)
    => row is each DataRow in datatable.
macro.AsEnumerable().FirstOrDefault(
    Function(r) r("PaymentMode") = row("PaymentMode") And r("Bank Name") = row("Bank Name") And r("RRN") = row("RRN")
) <- assign activity

and then replace row's "Macro Name" value by finded row's "Macro Name" value

try it!

5

Ya i tried but not getting desired result

6

@ClaytonM

7

Can you elaborate on what you are getting that’s not your desired result after trying the suggested line of code?

Essentially, you would assign the matched row to the current row in the ForEach, then assign the value of the matched row to the the column in that row.

However, you must also decide if you only want to assign that value in each of the matched rows (there could be duplicates) or only the First matched row or only the Last matched row.

I believe the suggestion was using FirstOrDefault() which would give you only the first row.

To match with all rows if there are duplicates, then use .Where() which I’m more used to implementing, but is used similarly.

matchedRows = macro.AsEnumerable().Where(
    Function(r) r("Payment Mode").ToString.Trim.ToUpper = row("Payment Mode").ToString.Trim.ToUpper And r("Bank Name").ToString.Trim.ToUpper = row("Bank Name").ToString.Trim.ToUpper And r("RRN").ToString.Trim.ToUpper = row("RRN").ToString.Trim.ToUpper ).ToArray

(the reason you need the .ToString is because if you don’t it tries to match them as objects which won’t work because each row references to a different data table. Also, the .ToUpper is so it’s not case-sensitive)

Then you want to check if it found any matches with .Count

IF matchedRows.Count > 0

inside the IF, you can choose to only use the first, last, or all rows. To assign to all rows you would place a ForEach inside the IF, like so:

IF matchedRows.Count > 0
    ForEach match In matchedRows //typeArgument: DataRow
        Assign: row("Macro Name") = match("Macro Name").ToString.Trim

If you choose to only do the first match then you can just use the index, like matchedRows(0) . Also, .FirstOrDefault() will probably work instead of .Where, so if you want to use that, and have issues, just let us know what errors you are getting.

Also, if you get errors on any of this, please let us know what it’s showing.

Regards.

8

hii @ClaytonM thanks for brief explaination. I got matched rows …now i want to see
if multiple matches are found, sum up
the instances of matches found and log it in column “Number of Matches”
can u help me with this??

Thank You

9

@ClaytonM i got matched rows count anyways thank you again

10

Hi @ShivuDundi

You can check this link i had posted the similar solution before

Hope it helps you

Regards

Nived N
Happy Automation

11

Thank You @NIVED_NAMBIAR :smile:

1 Like
12

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