Finding the last entry in a column based on criteria

[DKS]

I have a log, containing 4 columns. First 2 columns contain dept name and
team name respectively. Third column contains a timestamp at which the row
was added to the spreadsheet. By design, we always add a row at the end of
the existing list (thus an append, never an insert between existing rows).
The 4th column contains a value.

My need is that if I give a dept and team name combination (via 2 cells), I
would like to have the value of the 4th column returned for the
chronologically last entry in the list for the dept + team combination. WE
DO NOT NEED TO CONSULT THE TIMESTAMP COL, it can be assumed that records are
always added in chronological order and thus the last record with the DEPT +
TEAM combination is what I am looking for.

any ideas on how to do this without writing a macro?

Many thanks in anticipation.

 

[Hans Knudsen]

Try
=LOOKUP(2,1/(A1:A100&B1:B100=F1),D1100)
Where F1 holds department and team name combination.

Regards
Hans

 

[DKS]

Wow, incredible. I did not understand the logic behind the formula you have
proposed but it works like magic.

Thanks a lot.

PS: If you have the time & the inclination then I would love to know the
logic behind your formula.

 

[Hans Knudsen]

As far as I know the logic of the formula is due to Frank Kabel who
died
in a tragic accident in his home country of Germany back in
January 2005.
http://groups.google.com/group/micr...+kabel+group:*.excel*&rnum=1#0f66dda9d4e79425

So it is not my formula, but nonetheless I will try to explain, as
well as I can, how it works. If I do it wrong others will hopefully
correct me.

Try entering the following:

Row/Column
A B C D .... F
1 Acct 11 =A1&B1 30 Sales13
2 Mkt 12 =A2&B2 31
3 Sales 13 - " - 32
4 Mkt 12 - " - 33
5 Sales 13 - " - 34
6 Acct 11 - " - 35

In an empty cell: =LOOKUP(2, 1/(C1:C6=F1), D16)
You should get the result 34.

Try to highligt the following part of the formula: C1:C6=F1 and
press F9. You should see (FALSE, FALSE, TRUE, FALSE, TRUE, FALSE).
Now try to highlight the part of the formula that reads:
1/(C1:C6=F1). You should get: (#DIV/0!, #DIV/0!, 1, DIV/0!, 1,
DIV/0!)

Now read Excel help on the LOOKUP function. You will note that “The
values in lookup_vector must be placed in ascending order:
....-2, -1, 0, 1, 2, .... , A-Z, FALSE, TRUE.
#DIV/0! does not seem to be considered by the LOOKUP function.

The LOOKUP value (2) is not found.

Excel help further says: If lookup can't find the lookup_value, it
matches the largest value in lookup_vector that is less than or
equal to lookup_value.
The largest value that is less than or equal to lookup_value is the
last 1.

I hope having done justice to Frank Kabel and given you an
understanding of the formula.

Regards
Hans

 

[tigger]

Hi there,

I am using this formula to find the last entry with today's date in an array
in a bank statement tracking sheet, which shows the current balance of the
account based on today's date. This works perfectly when today's date is in
the array but returns #N/A when it isn't.

Is there a way to instruct the formula to return the latest balance prior to
today's date if no transactions have been recorded for today - e.g. the last
transaction is dated 15/1/08 but today's date is 16/1/08?

Thanks for your help.

 

[T. Valko]

Typically, the current balance is the last entry in the balance column so it
shouldn't matter what the date is. Just return the *last* entry from the
column:

Column F is the balance column

=LOOKUP(1E100,F:F)

 

[tigger]

Hi, thanks for your relpy.

I show uncleared balances as well so there may be other entries below the
last cleared entry.

I need it to calculate the last cleared balance entry and ignore anything
later than today's date.

Any ideas?

Thanks

 

[T. Valko]

I need it to calculate the last cleared balance entry and

ignore anything later than today's date.

Ok, so how do you denote "cleared" ?

In my register I use a simple "X".

So, all you should have to do if find the *last* X (or whatever method you
use to denote cleared) and the corresponding balance.

You can modify the original formula:

=LOOKUP(2,1/(C1:C6="x"), D16)

Where column C is the cleared column and column D is the balance column.

If this still doesn't work then you'll have to post an example so I can see
exactly what you mean.

 

[tigger]

I use the sheet to input future known transactions - some of them have known
dates (e.g. rent) and some are card transactions with a known transaction
date (but this is obviously a few days earlier than the date it clears the
bank). I don't actually mark them "cleared", although I could.

I've decided to cheat and use a VLOOKUP to find the nearest date to today's
date and hide this in another cell.

Thanks for your help anyway!

 

[T. Valko]

Ok, glad you got it worked out.