Find Last Date Invoiced for Each Customer

  • Thread starter Thread starter nytwodees
  • Start date Start date
N

nytwodees

I use Excel 2000.

I exported a file from Quickbooks to Excel that lists each customer in
alphabetic order followed by all the dated invoices from that
customer. The list in excel looks something like this:

CustName Invoice Date Amount

ABC Corp
5/22/2012 $500.00
5/03/2012 $300.00
Total: $800.00


BCD Corp
3/22/2012 $150.00
7/15/2011 $225.00
5/31/2011 $325.00
Total: $700.00

etc.

I want to show the last invoice dated (only) for each customer. The
report would look something like this:

CustName Invoice Date Amount

ABC Corp 5/22/2012 $500.00
BCD Corp 3/22/2012 $150.00
etc.

Is there an easy way to accomplish this?
 
nytwodees presented the following explanation :
I use Excel 2000.

I exported a file from Quickbooks to Excel that lists each customer in
alphabetic order followed by all the dated invoices from that
customer. The list in excel looks something like this:

CustName Invoice Date Amount

ABC Corp
5/22/2012 $500.00
5/03/2012 $300.00
Total: $800.00


BCD Corp
3/22/2012 $150.00
7/15/2011 $225.00
5/31/2011 $325.00
Total: $700.00

etc.

I want to show the last invoice dated (only) for each customer. The
report would look something like this:

CustName Invoice Date Amount

ABC Corp 5/22/2012 $500.00
BCD Corp 3/22/2012 $150.00
etc.

Is there an easy way to accomplish this?
Click to expand...

I suspect you didn't properly configure your custom report in QB before
exporting it to Excel. This would make your task easier if you sorted
the report by customer first, then by invoice date (descending) second
so the first invoice listed in your worksheet for each customer would
be the latest invoice issued to them. I'd be inclined to set up the
report to better facilitate your task criteria. This would allow you to
build a macro that would require minimal code to get the data how you
want it displayed.<g>

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
You could create a pivot report with the "Max. of Invoice Date" measure.

--
Sriram

"GS" wrote in message
nytwodees presented the following explanation :
I use Excel 2000.

I exported a file from Quickbooks to Excel that lists each customer in
alphabetic order followed by all the dated invoices from that
customer. The list in excel looks something like this:

CustName Invoice Date Amount

ABC Corp
5/22/2012 $500.00
5/03/2012 $300.00
Total: $800.00


BCD Corp
3/22/2012 $150.00
7/15/2011 $225.00
5/31/2011 $325.00
Total: $700.00

etc.

I want to show the last invoice dated (only) for each customer. The
report would look something like this:

CustName Invoice Date Amount

ABC Corp 5/22/2012 $500.00
BCD Corp 3/22/2012 $150.00
etc.

Is there an easy way to accomplish this?
Click to expand...

I suspect you didn't properly configure your custom report in QB before
exporting it to Excel. This would make your task easier if you sorted
the report by customer first, then by invoice date (descending) second
so the first invoice listed in your worksheet for each customer would
be the latest invoice issued to them. I'd be inclined to set up the
report to better facilitate your task criteria. This would allow you to
build a macro that would require minimal code to get the data how you
want it displayed.<g>

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Using helper columns assuming ABC Corp is in A2 you could put =IF(ISBLANK(A2),D1,A2) in D2 and =AND(ISBLANK(A1)=0,ISBLANK(C2)=0) in E2. Then, copy the formulae down.

This would put the Custname against each amount in column D and you'd then filter on TRUE in column E.

CustName Invoice date Amount
5/22/2012 $500.00 ABC Corp TRUE
3/22/2012 $150.00 BCD Corp TRUE

Crude but you can tidy it up yourself if presentation is an issue.

Hope this helps
 
Back
Top