What is the Fastest Way to Match One Column With Another Column

Hi All

Lets say I have an excel data that contains

Column 1 | Column 2
A | A
B | B
C | C
D | D
E | F
G | G
H | I

Column 1 and Column 2 supposed to match on each row ( A supposed to match with A, etc) so therefore this excel suppose to return that there are discrepancies on row E and H.

What is the fastest way to detect if there is discrepancy on this excel?

I prefer not to use for each row as I think that repeated loop on a lot of lines may result in a lot of processing time.

Thanks in advance


How about the following steps?

1.Read the table as DataTable using Read Range activity
2.Use following expression (LINQ).

arrResult = dt.AsEnumerable.Where(Function(r) r("Column 1").toString<>r("Column 2").toString).Select(Function(r) r("Column 1").toString).toArray

3.Finally, you can get discrepancy values in Columns 1 as {“E”,“H”} in arrResult.

If you want to just check whether there is discrepancy, the following will work in if condition.

dt.AsEnumerable.Where(Function(r) r("Column 1").toString<>r("Column 2").toString).Any


actually as you are talking about processing time, a simple loop comparing the columns is the fastest of anything people keeps suggesting in our forum…

Thanks. it works like a charm

1 Like

Is it true? because im afraid if i have to compare like hundreds or thousands of line, repeatedly going to each loop will take some time

Yes it is and i have over 20 years development experience to back me up on that :wink:

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