Find Last Billing Date for each Customer.

[shasi.shekhar@gmail.com]

Hi, All,


I have some 80000 rows of data for some 1600 customers, out of that I have to find last Billing date for each customer.


Sample Data is given as below. Please help me out.


Distributor Code Invoice Date

305226 20-Jan-12

305309 21-Jan-12

305314 22-Jan-12

305315 23-Jan-12

305218 01-Feb-12

305226 20-Feb-12

305309 21-Feb-12

305314 22-Feb-12

305315 23-Feb-12

305226 25-Feb-12

305309 26-Feb-12

305314 27-Feb-12

305315 28-Feb-12

305357 28-Feb-12

305226 29-Feb-12

305222 01-Mar-12

305309 01-Mar-12

305314 02-Mar-12

305315 03-Mar-12

305357 03-Mar-12

305222 15-Mar-12

305222 25-Mar-12

305226 25-Mar-12

305309 26-Mar-12

305314 27-Mar-12

305315 28-Mar-12

305357 28-Mar-12


Thanks,

Shashi Shekhar

 

[bobhc]

Good day

I put your data in to a spread sheet converted to a pivot table with both fields in the row area, all dates sorted by customer and then you can filter by customer, if you had more columns of data it would make the pivot work even better

 

[Faseeh]

Hi,


Assuming that codes are present in column A and dates in column B use this formula:

MAX(IF(A$1:A$27=A1,B$1:B$27))

...enter with ctrl+shift+enter and drag down. Format cell as date in which you enter this formula.


Regards,

Faseeh

 

[shasi.shekhar@gmail.com]

Thanks Faseeh.


You are rocking!!

 

[Faseeh]

You are welcome!


Faseeh