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).
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()
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 .
(From p in dt1.AsEnumerable()
(From q in dt2.AsEnumerable() where Convert.ToString(q.Item(“Column2”).Equals(Convert.Tostring(p.Item(“Column1”)))
It will return the list of column 1 values of dt2 where the column2 of dt2 is equal to column of dt1.
@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,
Run For Each Row in D1
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
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.
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.
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
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)