Compare rows between 2 datatables with complex condition

Hi Everyone.

I have a complex project to compare two from datatable as below details:

  • Table1 will compare row by row with Table2… one row of Table1 will compared to all row in Table2
  • If the value of “code” column is same and the value of “cost” column from 2 Table is > 0 or < 0 ( both of from 2 Tables ). Then it will create new row in the “Output” Sheet…
  • If the value of “code” column from Table1 not exist in Table2 or exist in Table2 but the “cost” value different ( >0 or <0 )… then it will create only row from Table1 into “Output” Sheet

The file has many row, I have tried to use for each row and succeeded but it very slow.

Has any way to do this by Linq?

Thanks in advance!
Book1.xlsx (10.2 KB)

Table1:

Table2:

Output:

Hello @Mr.H!

It seems that you have trouble getting an answer to your question in the first 24 hours.
Let us give you a few hints and helpful links.

First, make sure you browsed through our Forum FAQ Beginner’s Guide. It will teach you what should be included in your topic.

You can check out some of our resources directly, see below:

  1. Always search first. It is the best way to quickly find your answer. Check out the image icon for that.
    Clicking the options button will let you set more specific topic search filters, i.e. only the ones with a solution.

  2. Topic that contains most common solutions with example project files can be found here.

  3. Read our official documentation where you can find a lot of information and instructions about each of our products:

  4. Watch the videos on our official YouTube channel for more visual tutorials.

  5. Meet us and our users on our Community Slack and ask your question there.

Hopefully this will let you easily find the solution/information you need. Once you have it, we would be happy if you could share your findings here and mark it as a solution. This will help other users find it in the future.

Thank you for helping us build our UiPath Community!

Cheers from your friendly
Forum_Staff

Hi @Mr.H

Please try this,

Use a write range activity,

dt1 - datatable from sheet1
dt2 - datatable from sheet2
dt3 - from build datatable

(From dtRow1 In dt1.AsEnumerable
From dtRow2 In dt2.AsEnumerable.where(Function(row) row("code").ToString.Equals(dtRow1("code").ToString) And
((CInt(row("cost").ToString) >= 0 And CInt(dtRow1("cost").ToString) >= 0) Or
(CInt(row("cost").ToString) < 0 And CInt(dtRow1("cost").ToString) < 0)))
Let code1 = dtRow1("code").ToString
Let qty1 = dtRow1("quantity").ToString Let cost1 = dtRow1("cost").ToString
Let des1 = dtRow1("Des").ToString Let qty2 = dtRow2("quantity").ToString
Let cost2 = dtRow2("cost").ToString Let des2 = dtRow2("Des").ToString
Select dt3.Rows.Add({code1,qty1,cost1,des1,qty2,cost2,des2})).CopyToDataTable

and then use an append range activity,

(From dtRow1 In dt1.Select
Group By k = dtRow1("code").ToString Into grp = Group
Where Not dt2.AsEnumerable.Any(Function(x) x("code").ToString.Equals(grp(0)("code").ToString)) And (CInt(grp(0)("cost").ToString) >0 Or CInt(grp(0)("cost").ToString) < 0)
Select  grp(0)).CopyToDataTable

Output

image

test.xaml (10.7 KB)

Thanks

1 Like

Hi Bro.

Sorry for late reply.

Thanks you very much!

1 Like

Hi Bro.

Sorry i have a concern about your code.

I want to compare one by one row in Sheet1 with all of rows in Sheet2… But in your code, i feel it compare each row in Sheet2 with rows in Sheet1 right ?

Pls make clear to me.

Thanks in advance!

@Mr.H Let me explain what it is doing please let me know if it deviates from your requirement,

  1. It will loop the rows in sheet1 (dt1).
    first it will take the first row in sheet1
  2. then it will check the column code of the row with all the rows in the sheet2(dt2) and also it compares if the cost is both positive and negative (compare both dt1 and dt2)
    3.if all the condition matches it will add a row (combination of sheet1 and sheet2)

then it will do the same procedure for second row in sheet1 and so on until last row.

it looping first sheet rows and check if that specific row is present in sheet2.

You can only use write range and check if the results are matching.

Thanks

1 Like

Thanks you very much Bro!

1 Like

Hi Bro.

Sorry, i have added one row into the Sheet1 ( red highlight )… but this row not added into the result Sheet.

Could you pls check!


Book1.xlsx (11.3 KB)

Hi @Mr.H

Is the code vat present in sheet2?

If it is not present it will create only one row right?

Thanks

1 Like

Hi Bro.

It not present in Sheet2… then it will take all of that row from sheet1 and add into the result sheet.

Any row in Sheet1 not present in sheet2, it will bring row in sheet1 into the result sheet.

If the sheet1 has 2 row “vat” , it also create 2 time… not only one.

Thanks you!

@Mr.H yes i understood, will tweak the code and come again

Thanks

1 Like

@Mr.H Have changed it please check now and let know if any changes required,

  1. Dont need to change anything in write range
  2. Add remove duplicates activity after write range and pass dt1.
  3. In append range use this code,
(From dtRow In dt1.AsEnumerable
Where Not dt2.AsEnumerable.Any(Function(x) x("code").ToString.Equals(dtRow("code").ToString)) And (CInt(dtRow("cost").ToString) >0 Or CInt(dtRow("cost").ToString) < 0)
Select  dtRow).CopyToDataTable

Thanks

1 Like

Hi Bro.

Thanks you very much for your strong support!

I think dont need to use Remove Duplicate Row, because each row in sheet1 will compare all of rows in Sheet2, if same the “code” value, it will create row base on value from both of sheet1+Sheet2, if not match it will create row base on value from Sheet1 only.

@Mr.H of table 1 has exact rows (all 4 column has same values) you still need to add them? Then you can remove the remove duplicates activity.

Thanks

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