Checking if one data from an excel matches another , then getting the crossponding value

Help
,
1

I have 2 excel sheets , where one of them has one(dt1) column and the other has 2(dt2). I want to check if the value in dt1 matches the value in dt2 ( in the 2nd column) then get the value from it’s corresponding column ( which is (the 1st column).

Here what I have so far:
I have read both the excel file using read range and stored it to a variable data table
I used for reach loop to get the each row from dt1 and then used if else statement to check if the value exist in dt2( column 2). I got stuck here, how do I specify the robot to look at dt1 value in dt2 data table and which expression do I use to get the corresponding value(column 1 of dt2) if dt1 value is found in dt2(2nd column).

2

@Dominic any thoughts on this?

3

Hi @sherpa24,
Use the below code to get the matching records from another datatable.
Datatable result = DT1.AsEnumerable().Where(function(row) DT2.AsEnumerable().Select(function(r) r.Field(Of String)("Column1")).Any(function(x) x = row.Field(Of String)("Column1"))).CopyToDataTable()

Regards,
Arivu

4

Datatable->Dt1
Datatable->D2
User for each row activity ->Dt1

use IF activity
Dt2.Select("column1= '"+row("column1").Tostring()+"'").length>0

If value returns true then the value exists in the 2nd table

Regards,
Arivu

1 Like
5

@sherpa24, This one looks good as suggested by arivu.

Regards,
Dominic :slight_smile:

6

ARE BOTH column1 from the same table or is it just from dt2?

7

@sherpa24, as you are running for each for dt1, row(“column1”).tostring is from dt1.

And we are trying to select the matching values from dt2 column 1 with dt1 column1.

Regards,
Dominic :slight_smile:

8

what if my column1 in dt1 does not have a header. A1 starts with value.

9

@sherpa24, still you can access with default column index like column0,column1,2,3 and so on.

Regards,
Dominic :slight_smile:

10

DatatableMatch.xaml (20.8 KB)
for some reason it is giving me a null value exception. could you elaborate the issue.

11

Hi @sherpa24,
Use Convert.ToString(row(“column1”)) for checking condition

12

Hi @sherpa24,

I have modified your code use this one, if you face any problem let you know
DatatableMatch.xaml (20.7 KB)

Regards,
Arivu

1 Like
13

okay if I am looping through the d1 rows and checking if it matches the d2 row , how do I get the value of another column in d2 which corresponds to the matched value of d1.
Here is an example
d1 = column 1 (1234),(3456) etc …
d2 = column 1 (1234),(6432)etc
column 2 (ABC),(BCD) etc
value of d1 matches D2 in column 1 , but we want to output the value (ABC) which is in the same row as (1234) but different column .

Thank you

14

@sherpa24

Try this,

(From p in dt1.AsEnumerable()
select
(From q in dt2.AsEnumerable() where Convert.ToString(q.Item(“Column2”).Equals(Convert.Tostring(p.Item(“Column1”)))
select Convert.Tostring(q.Item(“column1”)))).ToList

It will return the list of column 1 values of dt2 where the column2 of dt2 is equal to column of dt1.

Regards,
Mahesh

15

@sherpa24, we have LINQ to solve your requirement. But as you are in a need to learn how datatable works, am sharing this from what I have understood.

Datatable is like a matrix where we can access the row and column with same mathematical logic.

dt.Rows(0)(0) - First Row First Column
dt.Rows(0)(1) - First Row Second Column
dt.Rows(1)(0) - Second Row First Column
dt.Rows(1)(1) - Second Row Second Column

Let’s take this above example,

  1. Run For Each Row in D1
  2. Inside body of for each row place a If Condition and check this condition,
    row("Column1").ToString.Equals(D2.Rows(D1.Rows.Indexof(row))("Column1"))
    Here we are taking the D1 table’s row reference (Index) to get the D2 table rows value
  3. in Then part of if condition place a Message Box and paste this condition,
    D2.Rows(D1.Rows.Indexof(row))("Column2").tostring
    This will take value from D2 column2 when column1 from D1 and D2 matches.

NOTE: You can go through our forum posts regarding excel automation and search for solved xamls where you will get a detailed and clear understanding.

Regards,
Dominic :slight_smile:

16

there are no erros in the code but the condition is never true. eveytime ti executes the else statement.

17

125
1251023×801 27.4 KB

here is the condition with than and else
is there anything wrong with the condition because it always outputting the else ( message box) = ( it does not exist)

Thank you

18

Hi @sherpa24,

in If Condiition change
DT_22.Select("gui='"+Convert.ToString(row("Ship To"))+"'").Length>0

Regards,
Arivu

1 Like
19

@sherpa24, Can you use

  1. Trim after every .Tostring
  2. and use StringComparison.InvariantCultureIgnoreCase in .Equals.

Example : row(“Column1”).ToString.Equals(“sample”, StringComparison.InvariantCultureIgnoreCase)

This might be also because of empty spaces or upper/lower case conversion.

Note above solution shared by me will check and compare the same row index. If you want check that row value matching with any of the row in other datatable then LINQ query will be the best and it has been already shared by our forumers.

References : Compare Datatables one specific column with other specific Column and Get Matched and Not Matched Records`

Regards,
Dominic :slight_smile:

20

I got the cross reference to work at the end but while looping , at the end when the bot doesn’t find any row to loop it should automatically step to next sequence. That doesn’t happened instead it gives me this this error at the bottom.
any reasons why?
This doesn’t not happen if I specify the exact number of row in the ( read range) to read. but if I specify like (A:A) which is all the rows in that column it gives me the errors.

ExcelColumnFormatting has thrown an exception

Source: If

Message: Cannot perform ‘=’ operation on System.Double and System.String.

Exception Type: EvaluateException

System.Data.EvaluateException: Cannot perform ‘=’ operation on System.Double and System.String.
at System.Data.BinaryNode.BinaryCompare(Object vLeft, Object vRight, StorageType resultType, Int32 op, CompareInfo comparer)
at System.Data.Select.Eval(BinaryNode expr, DataRow row, DataRowVersion version)