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.

(E-Mail Removed) wrote:
>
> 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

--

Dave Peterson

Thank you very much you for your help. I am still working on it.

Dave Peterson wrote:
> 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.
>
> (E-Mail Removed) wrote:
> >
> > 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
>
> --
>
> Dave Peterson