[RESOLVED] Datatable select rows after value and before value (multiple headers in rows)

[Fedaykin]

I'm pulling an Excel file into a Datatable and the data ends up like:


Column A | Column B
_______________________________________
Retirement Plan: A |
Employee | Employee deduction
Ralph | $100
Sue | $100
Bob | $0


Retirement Plan: B |
Employee | Employee deduction
Ralph | $0
Sue | $150
Bob | $0
_____________________________


So, I need to add Sue's deduction into the proper Retirement plan in the database.

I have another datatable with all employees name in it so I can loop through it and compare to the names in this datatable to get the deduction, however since there are multiple column headers in the rows (Retirement Plans) I don't know how to figure out which Retirement plan is above Sue's name when I find it in the For Each loop.

Since I know the Retirement plan names (A, B) I could just do a LINQ Select to find the retirement plan row and then a Do until I find a blank row between the retirement plans? Seems sloppy?

Anyone know a slick way to separate this out when the headers end up like this?

[Fedaykin]

Okay, I ended up doing it like this:

Dim drp1() As DataRow = dt.Select("A = 'Retirement Plan: A'")
        If drp1.Any Then
            Dim i As Integer = dt.Rows.IndexOf(drp1(0)) 'Get the index of the datarow for 'Retirement Plan: A'
            Dim EmpName As String = Nothing
            Do Until EmpName = "Total" 'There is a 'Total' in the column below each group of employee names that I didn't include in my example, but you could stop on blank or the next 'Retirement Plan B'
                EmpName = dt.Rows(i + 2).Item("A").ToString 'i+2 just because employee names start 2 rows below the Retirement plan name
                i = i + 1
                'do stuff
            Loop
        End If

Let me know if anyone has a better way