Jul 08 2019 07:07 AM - edited Jul 09 2019 12:39 AM
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
I have a
Jul 08 2019 08:18 AM
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
Jul 08 2019 11:14 AM
With INDEX/MATCH that could be
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)
Jul 09 2019 12:38 AM
Jul 09 2019 12:39 AM
Jul 09 2019 01:24 AM
@PerMorris , you are welcome