Easier way to find matching rows in a datatable while retaining the original row index?

Help Something Else
1

I have multiple datatables stored in a dictionary (of string, datatable) because the number of sheets in the source Excel file varies. I use For Each to loop through the dictionary keys and then For Each Row in Datatable to loop through the datatable within each. When I find the row I’m looking for I use Add Data Row to certain data about the loan to a working datatable (loansDT). I then repeat both loops to find any other rows that match the Customer Number from the first loop and add them to loansDT.

As I’m doing this I’m storing the sheet name and original row index in the row in loansDT because in the end, I will need to go back and use Write Cell to update a value in the original sheet and row in the Excel file.

This is all fairly time consuming, repeatedly looping through multiple datatables. I could use Filter Data Table into two temp datatables (ie original loan, child loans) to do it all, but I don’t see any way to retain the original row index that way.

Ideas?

2

@postwick

Use filters and get the data and to get the row index you can use the below query on original table

Dt.Rows.IndexOf(dt.AsEnumerable.Where(function(x) String.Join(",",x.ItemArray).Equals(String.Join(",",requiredrow.ItemArray))).First)

Here required row is the currentrow in your final loans datatablr and dt is the original table

I am trying to match each column value joined with string and then get the row index of matched row from first table

I hope I understood it correct

Cheers