finding last transaction in month

[Ben]

Hello,

I have a sheet that contains transaction data downloaded from 2
different credit card sites. It is formated so that the 1st column
contains the date and it is organized so that each month's transactions
are grouped together. I.e. all of July for example will be together
though not necessarily sored by day of the month and there can
potentially be blank lines separating transactions.

What I need to do is find the last transaction line for a particular
month and get a value from that row. I currently do this:
VLOOKUP(K$1, Spending!$A3:$P1500, 16)

Where K1 is the last day of the month for the month I am after (E.g.
July 31, 2009). The problem with my solution is that while each month is
grouped together in ascending order, the days of the month are not
necessarily ascending. E.g. for the end of last month the last 3
transactions occurred on Jul 31, Jul 30 and Jul 31 (in that order) and
the vlookup only returned the 3rd to last transaction instead of the
last. I was wondering if anyone could suggest a solution to my problem?

Thanks,
Ben

 

[T. Valko]

VLOOKUP(K$1, Spending!$A3:$P1500, 16)

Try this:

=LOOKUP(2,1/(Spending!A3:A1500=K1),P31500)

 

[Dave Peterson]

I bet Biff meant:
=LOOKUP(2,1/(Spending!A3:A1500=K1),Spending!P31500)

 

[Ben]

I appreciate your help. The below solution works with the exception of
one case though - where there was no transaction on the last day of the
month it produces #N/A instead of the last transaction. For example, in
February the last transaction was on Feb 27 and not 28 so produced the
#N/A result instead. What would need to be tweaked to get around that?

Thanks again,
Ben

 

[Dave Peterson]

Just check to see if there's at least one instance first.

=if(countif(spending!a3:a1500,k1)=0,"",lookup(.....

 

[T. Valko]

I bet Biff meant:

I hope you doubled-down on that wager.

Thanks, Dave!

 

[Ben]

It seems that if that date does not exist, then I have to check the next
lowest and so forth until I get to 1 assuming that a date in the month
exists which would be quite a long line. Alternatively, I could
artificially insert a last day of month dummy row maybe.

 

[Dave Peterson]

Personally, I'd add that dummy row.

Biff, what would you do?

 

[Ben]

Actually, I thought of a way of automating the dummy row invisibly. I
already have a hidden column with the date in it for when there are
blank lines (with no transactions) as I still need to know what the
current month is so that certain fields are reset when the month changes
(I put a blank line between months usually and so I couldn't compare
consecutive rows dates to see if they are different since one would
potentially be blank. I also cannot assume that a blank line means a
change of month since a blank line could appear in the middle of a month
too). So I store the last date in that column and it carries it over
blank lines. All I need to do is change the search column from col A to
that column and instead of storing the previous date store the end of
month by default. (I hope that is clear)

Thanks for your help,
Ben

 

[T. Valko]

Biff, what would you do?

If I understand....

Find the *last (bottom-most) max* date of the month...

Dates in A1:A20
Value to be returned in B1:B20

F1 = any date for the lookup month/year like 7/1/2009 to lookup dates in
July 09

Array entered** :

=LOOKUP(2,1/(A1:A20=MAX(IF((MONTH(A1:A20)=MONTH(F1))*(YEAR(A1:A20)=YEAR(F1)),A1:A20))),B1:B20)

No error checking!

...........A..............B
1...7/22/2009......a
2...7/27/2009......b
3...7/14/2009......c
4...7/27/2009......d
5...7/10/2009......e

7/27/2009 is the max date for July 09. A4 is the last instance of the max
date.

Result = d

We can shorten that a bit if we use a range of lookup dates:

F1 = 1st of the month = 7/1/2009
G1 = end of month = 7/31/2009

Array entered** :

=LOOKUP(2,1/(A1:A20=MAX(IF((A1:A20>=F1)*(A1:A20<=G1),A1:A20))),B1:B20)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Dave Peterson]

I'm not sure that this will work -- but maybe I misunderstood the original post.

I didn't think that the data was sorted in date order based on this phrase:

"...for the end of last month the last 3 transactions occurred on Jul 31, Jul 30
and Jul 31 (in that order)..."

If that last Jul 31 entry wasn't made, then the last transaction on the list
would be Jul 30 (not Jul 31).

Maybe...

=INDEX(B1:B20,MAX(IF((A1:A20>=F1)*(A1:A20<=G1),ROW(A1:A20))))
(still an array formula (ctrl-shift-enter))

 

[Ben]

Hello.

I need the bottom most date of the month regardless of its value. I.e.
not necessarily the maximum - just what ever is there.

Thanks.
Ben

 

[T. Valko]

I need the bottom most date of the month regardless of its value.

Ok, I guess that makes it pretty clear!

Maybe...
=INDEX(B1:B20,MAX(IF((A1:A20>=F1)*(A1:A20<=G1),ROW(A1:A20))))

Yes, that'll work. If the data doesn't start on row 1 then you'll have to
add an "offset correction":

=INDEX(B5:B20,MAX(IF((A5:A20>=F1)*(A5:A20<=G1),ROW(A5:A20)))-ROW(A5)+1)