How to compare two excel, row by row

Help Studio
, ,
1

Hi
After a series of scraping, i have two DataTable.

I have to make a comparison on the first 4 columns and:

  • only if all rows are equal, perform an action.
  • if one of the list is different, take another action

image

The formatting of the Submitted amp column is different between the two DTs.

Can haelp with this?

A

2 Likes
2

Do you only need the first 4 columns to match? I noticed that Column E and F are different for each of the extracted datasets.

3 Likes
3

Yes, Only the first four columns.
Column 4, (submitted amt) contains an amount
that is formatted with different thousands of decimal symbols.

The last two columns are used for other subsequent operations.

2 Likes
4

Hi

Take two for each rows loop and use if condition inside the for each rows loop.
Use the below condition dtTable1.rows(Puchase_Odrer)=dt.Table2.Rows(Puchase_order ) AND dtTable1.rows(Line#)=dtTable2.(Line#) AND dtTable1.Rows(Submitted Amt)=dt.Table2.Rows(Submitted Amt)

2 Likes
5

I have the problem with the formatting of the column of the Submitted Ammount?
They are different.

2 Likes
6

Use the below formula in if condition.
row(“Purchase_Order”).ToString=dt2Row(“Purchase_Order”).ToString AND row(“Line#”).ToString=dt2Row(“Line#”).ToString AND
row(“Submitted Amt”).ToString=dt2Row(“Submitted Amt”).ToString

If you are facing issue for amount column while comparing , convert that row value to integer and compare that row value.

If you facing error let me know

2 Likes
7

I had some problems with using column indexes.
I modified the input like this:
Coupa.Rows (0) .Item (0) .ToString = TSnow3.Rows (0) .Item (0) .ToString And
Coupa.Rows (0) .Item (1) .ToString = TSnow3.Rows (0) .Item (1) .ToString And
Coupa.Rows (0) .Item (3) .ToString = TSnow3.Rows (0) .Item (3) .ToString

It Works…
By adding the account check, I have an error.

image

2 Likes
8

I wrote a line with the two ammount.tostring.

image

I should uniquely format the two amounts.

2 Likes
9

Hi
Use Below Formula
Coupa.Rows (0) .Item (0) .ToString = TSnow3.Rows (0) .Item (0) .ToString And
Coupa.Rows (0) .Item (1) .ToString = TSnow3.Rows (0) .Item (1) .ToString And
Convert.ToDecimal(dt1.Rows(0).Item(2).ToString)=Convert.ToDecimal(dat2.Rows(0).Item(2).ToString)

You have used wrong index for Submitted Amt column . Submitted Amt column index is 2 . (All index value start from 0).

You have ti use counter value for Rows and increase value end of each iteration

2 Likes
10

@AaronMark I hope issue is resolved. If its resolved Kindly mark it has resolved. If u still facing issue let me know

1 Like
11

Not work.
I used a WriteLine to understand what he wrote.
It gives me an error like this:
image

When i use this string in IF comand
image

But the comparison fails.
I read somewhere that I should use a Double.Parse.
But I have no experience with the controls.

1 Like
12

Could you share ur excel files , so I can develop that code for you.

1 Like
13

ok…
LogINServiceNOW.xaml (54,3 KB)

It probably contains some errors. Or better,
almost certainly some operation could have been done differently.
I don’t have much experience, and I’m going through small steps.
It works … “almost”.
Small details are missing for the happy path … let’s say so.
:slight_smile:

1 Like
14

Hi All,
I have a question here i.e., rather passing the column headers, can we dynamically loop through the two sheets row column values. The requirement here is - the excelOne sheet contains x columns (dynamic) and the excelTwo also contains x columns but column order might be different but headers are the same, and there is an unique values in one column and those values are same in two sheets. I need to compare two sheets rows one by one with the Unique key reference and if any deviations in values of the row columns, i have to display to the user, and the user will make the action like whether he would be conite with either sheetOne or sheeTwo values. Awaiting for solutions from expertise. Thanks all.