Datatable row comparison

Help
, ,
1

Hello everyone,

I’m having some doubts regarding the comparison of rows in the same datatable. My object is to compare all rows in the same datatable, but only compare the ones that have the same value in the column 1. If the values match i would like to change the row type to Good(green color) and they didnt match to Neutral(yellow color). Atm i’m reading my excel file saving in a datatable and sorting it by the column I want to make the comparisons from, but after that i’m kinda lost.
Anyone can give me some tips pls.

Thx in advance and best regards,

Compare two data tables in different excel files
2

Use DataTable Select to get the Rows with Same values into a DataRow Array

dtTable.Select("Name = 'abc'")

Iterate the datarow Array

get the row Index — dtTable1.Rows.IndexOf(row)+2 ( +2 is Index in Excel)

add Set Range Color inside foreach loop and apply color to cell

If for entire row : "A"+strIndex+":M"+strIndex

1 Like
3

Thx for the answer. But the thing is, I receive an excel with data, the first column is an ID that can have equal values or not, so I need to compare all first column to see which rows have the same ID value and then I need to compare the rest of the columns to the rows that have that same ID(from the column 1).

In the comparison if in the same column those rows have equal values I wanted to be green and if they are different I want them to become yellow. So in the end i will have a colored table where I can see witch parameters are OK and which ones NOT OK

4

Keep the above logic in Foreach Dt.Rows and in select do dtTable.Select(“ID=”+ row(“ID”).ToString)

After the above logic remove the duplicates, so you can proceed with non duplicates.

@tpernes dtArray.zip (8.2 KB)

1 Like
5

I guess I didnt explained myself clearly. Example.xlsx (8.7 KB)

I hope with this example I can explain my idea.

Thx again :wink:

1 Like
6

Anyone have another ideas? I’m kinda stuck.

Thx in advance.

1 Like
7

short question.
if the offset cell does not match, all the rows with that id should be marked as wrong?

1 Like
8

If there are no match for those I just want the program to jump to the next line until he finds matchs.
After he find matchs on the first column I want to check for each column if the values for those rows are the same or not.
If they are the same I wante the cell to become green and if they aren’t the cell will be colored as yellow.

Regarding the N/A I would like to create an option that when the N/A is analysed it’s just ignored because that specific document doesn’t have that value.

1 Like
9

i attached a sample workflow.
the logic is: you do a loop in all rows of the datatable and try to Select all faulty rows (ID to be the same but the other columns to have different values). if the Select operations returns at least 1 it means it should be flagged as wrong.

The attached sample does not contain the coloring part and it does the check only for column “B”. but I think it might be a start…
Main.xaml (8.4 KB)

1 Like
10

Thx it’s a good start.

If I have more columns that need to be checked I need to add them in the SELECT on if statement right?

Like I have several columns and if any of those rows fails in one columns they aren’t good anymore.

1 Like
11

yes, that would be the logic.
all other columns except ID must have the “<>” operator

**ID=**aaaa AND **Col1<>**bbbb AND **Col2<>**cccc AND **Col3<>**dddd …

edit. actually you might also need to drop some “OR” besides “AND”. I presume if any of the columns does not match there should be a flag, wright?

1 Like
12

Exactly

13

@vvaidya Can you suggest something please?

14

Can you run a macro?

15

I dont know how to work with macros. :disappointed_relieved:

16

@SHAISTA,

Can you check this ? Even I dono macros, so I have done an UIAutomation as a Temporary Solution. Expecting from someone to answer this in macro :exclamation:

ExcelMerge.zip (10.8 KB)

17

Please refer this

1 Like