SOLVED! Find certain table within data sheet and the find last row in that table

Copper Contributor

Hi, I have a problem that I can't find the solution to. I have (as you can see in the picture bellow a data sheet with different tables. Each table is divided by 2 empty rows as you can see. There are a lot of tables 50.000-100.000 rows. So I can ofcause use index match to find the correct table, using the ID, no problemo so far. Now my problem is that I need to find the last row in that table and there are different number of rows in the tables, as you can see in the picture. So I need to get the salary for that ID which would have been easy if it was the same amount of rows in all tables but it's not? /Per

Excel problem.JPG

 

I have a

5 Replies

@PerMorris 

Hi,

i write an userdefined function to do the job. See attached file.

 

Function GetLast(strKEy As String) As Double
Dim rngFind As Range
Dim lngLastRow As Long

Set rngFind = ActiveSheet.Range("B:B").Find(what:=strKEy, lookat:=xlWhole)

If Not rngFind Is Nothing Then
lngLastRow = Cells(rngFind.Row, "B").End(xlDown).Row
GetLast = Cells(lngLastRow, "E").Value

Else
GetLast = 0
End If

End Function

 

Best regards

Bernd

The vba-Tanker - a database full of usefull macros

@PerMorris 

With INDEX/MATCH that could be

image.png

in H5

=INDEX($E:$E,MATCH(1,INDEX(--ISBLANK(INDEX($B:$B,MATCH($G5,$B:$B,0)):$B1000000),0),0)+MATCH($G5,$B:$B,0)-2)
Absolutely brilliant, you saved my day! Thanks a lot.

/Per
Big thanks Bernd, worked brilliantly!