Last transation info

  • Thread starter Thread starter NH
  • Start date Start date
N

NH

I have a spreadsheet with 3 cols.
Col A contains the customer Number
Col B contains the Month-Year(mmm-yy)
Col C contains Dollar amounts

This amount colmn tells me the total payments I received from that
particular customer during a particular month. Hence if a customer pays
every month there are 12 rows of data. I have this data from year 1999 to
year 2004

What I need is the last time a particular customer paid me; which means if
customer# 10418 last paid me on Aug-99 I need in a separate sheet the
following output

Customer # Last paid month
10418 Aug -99

Using Advanced filter I can get unique records of customer #. How do I
extract the last payment data info?

Thanks in advance
 
Using AutoFilter, insert this into row 2 of an open
column, press ctrl/shift/enter, and fill down:

=C2=MAX(IF(A2=$A$2:$A$500,$C$2:$C$500))

Now select all columns, Data > Filter > AutoFilter, and
select TRUE in the formula column.

HTH
Jason
Atlanta, GA
 
Hi

You can use an array formula for this. Here is a small
example

Assume Range A2:A11 contains the customer number and range
B2:B11 contains the date of the payments. (Yours will be
much larger obviously :o))

On the same sheet you list the customer numbers (You can
use this on another sheet but you will just have to
include the sheet name in the formula)

Assume the customer numbers start in Cell D2 enter this
array formula (Ctrl+Shift+Enter)and then copy down.

=MAX(IF($A$2:$A$11=$D2,$B$2:$B$11,""))
Note:
This will return zero if the customer is not in the list
so you may want to hide this column and add another column
with an if statement to exclude any zeros

Hopefully this will work.
 
Thanks Nick Works fine
Nitin

Nick said:
Hi

You can use an array formula for this. Here is a small
example

Assume Range A2:A11 contains the customer number and range
B2:B11 contains the date of the payments. (Yours will be
much larger obviously :o))

On the same sheet you list the customer numbers (You can
use this on another sheet but you will just have to
include the sheet name in the formula)

Assume the customer numbers start in Cell D2 enter this
array formula (Ctrl+Shift+Enter)and then copy down.

=MAX(IF($A$2:$A$11=$D2,$B$2:$B$11,""))
Note:
This will return zero if the customer is not in the list
so you may want to hide this column and add another column
with an if statement to exclude any zeros

Hopefully this will work.
Click to expand...
 
Since the date data will be in an increasing order, a faster retrieval is
also possible:

=LOOKUP(2,1/(($A$2:$A$8=E1)*($B$2:$B$8)),$B$2:$B$8)

where E1 houses a customer number of interest like 10418.
 
Back
Top