Searching a Data Table using another data table

[Russell_J]

HI,
I’ve got a rather complicated task. I’m looking to scrape data from two excel sheets in an excel file into data tables. Then I want to identify rows in a single column (from the different data tables) that contain the same word (team names in this case). I then want to be able to complete calculations using data from other columns in the identified rows of both data tables.

I have no problem getting the data from excel into the data tables.

My attempted solution was to create a third data table that held the single name that is common between the two data tables for every row, I then used 3 overlapping “for each row” activities to find the index of each matching row in the 3 data tables (one set of teams, second set of teams, and the overlapping team names). Within the final “for each row” activity there is an if statement that tries to convert the index of a data table row to a string and then ask if the other data table contains that string.
DT_1.Rows(Index1)(0).ToString.Contains(DT_OLNames.Rows(IndexNames)(0).ToString AND DT_2.Rows(Index2)(0).ToString.Contains(DT_OLNames.Rows(IndexNames)(0).ToString

After this IF statement, I would perform my calculations and append them to an excel sheet.

This IF statement does not work and only returns what I assume to be the index numbers, several times over due to my triple for each activities.

Any thoughts or help would be appreciated.
Thanks!

[Yoichi]

Hi,

Hope the following sample help you.

arrIndex1 = dt1.AsEnumerable.Where(Function(r) dt2.AsEnumerable.Select(Function(r2) r2("TeamName").ToString).Contains(r("TeamName").ToString)).Select(Function(r) dt1.Rows.IndexOf(r)).ToArray()

Sample20211108-1.zip (2.6 KB)

Regards,

[Russell_J]

Hey,
Thanks so much, my code doesn’t give me a headache to look at anymore. I was just wondering how I would convert “arrIndex1” to be used as an actual index to find the row I’m looking for.
E.g.
“Datatablename”.Rows(“rownumber”).Item(“columnName/columnNumber”)
where rownumber is arrIndex1

Thanks again.

[Yoichi]

Hi,

The arrIndex1 has actual row index in dt1.
So, if you want to process items at the arrIndex, the following will help you.

Regards,