Creating a new list with Last Order Date

  • Thread starter Thread starter tr2usa
  • Start date Start date
T

tr2usa

I do have a customer transaction history. I would like to get the
latest order date with amount in to another sheet instead of all the
orders under same account number and bring the rest of the
corresponding info to the other columns. Such as bill to information
for my new sales rep. Can someone help me?

Thanks
Vedat

A2=Customer Number
B2=Order numbers
C2=Order dates
D2=Order Amounts
D3=Bill to Name
etc.

A2 B2 C2 D2
CA123 1234 10/11/06 $500
CA123 2345 09/10/06 $200
CA123 6785 06/06/05 $300
 
I think I would do my best to keep all the data in one location. I'd add a
formula to another column and then use data|filter|autofilter to just show those
"max dates" rows.

I put this in E2:
=IF(C2=MAX(IF($A$2:$A$20=A2,$C$2:$C$20)),"MAX","")

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

Then drag down column E.

=====
But if you really wanted to, you could apply data|filter|autofilter to that
column and then show just those "max" entries. Then copy those visible rows to
a different worksheet.
 
Back
Top