Latest date

[ChristinaC]

Can someone help me with this one. I have a spreadsheet with a list of
Purchase orders that have more than one line. Eg

Po No Line Date
no
po12345 1 18/02/2004
po12345 2 05/04/2005
po12345 3 16/02/2003
po12345 4 06/02/2004
po12346 1 09/02/2005 etc.....

Can someone show me what code I need to use to find the latest date in the
third column against each po number. Hope this makes sense.

[Tom Ogilvy]

Can the data be sorted on Po No and Date. Can a formula be put in the next
adjacent column. Where do you want the output? I assume this is a list of
unique Po No with corresponding latest dates?

--
Regards,
Tom Ogilvy

"ChristinaC" <[email protected]> wrote in message
news:[email protected]...
> Can someone help me with this one. I have a spreadsheet with a list of
> Purchase orders that have more than one line. Eg
>
> Po No Line Date
> no
> po12345 1 18/02/2004
> po12345 2 05/04/2005
> po12345 3 16/02/2003
> po12345 4 06/02/2004
> po12346 1 09/02/2005 etc.....
>
> Can someone show me what code I need to use to find the latest date in the
> third column against each po number. Hope this makes sense.

[AA2e72E]

In a cell formatted as dd/mm/yyyy, type this formula:

=LARGE("A1:A4",1) ' Assumes your dates are in A1:A4

"ChristinaC" wrote:

> Can someone help me with this one. I have a spreadsheet with a list of
> Purchase orders that have more than one line. Eg
>
> Po No Line Date
> no
> po12345 1 18/02/2004
> po12345 2 05/04/2005
> po12345 3 16/02/2003
> po12345 4 06/02/2004
> po12346 1 09/02/2005 etc.....
>
> Can someone show me what code I need to use to find the latest date in the
> third column against each po number. Hope this makes sense.

[Bob Phillips]

=MAX(IF(A1:A100="po12345",C1:C100))

which is an array formula, committed with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


"ChristinaC" <[email protected]> wrote in message
news:[email protected]...
> Can someone help me with this one. I have a spreadsheet with a list of
> Purchase orders that have more than one line. Eg
>
> Po No Line Date
> no
> po12345 1 18/02/2004
> po12345 2 05/04/2005
> po12345 3 16/02/2003
> po12345 4 06/02/2004
> po12346 1 09/02/2005 etc.....
>
> Can someone show me what code I need to use to find the latest date in the
> third column against each po number. Hope this makes sense.

[AA2e72E]

The first attempt is lacking! (Did not consider Pol No.) Try this:

Sub LatestDate()
Set adors = CreateObject("ADODB.RecordSet")
Cnn = "Provider=MSDASQL;Driver={Microsoft Excel Driver
(*.xls)};DBQ=c:\latest.xls;"
Sql = "SELECT [Pol No],max(Date)as [Latest Date] FROM [SHEET1$] GROUP BY
[Pol No]; "
adors.Open Sql, Cnn
While Not adors.EOF
Debug.Print adors("Pol No").Value & adors("Latest Date").Value
adors.Movenext
Wend
End Sub

1. If you are using a workbook that is open, specify DBQ accordingly.
2. If your data is in a range, name that ranhe and specify it instead of
Sheet1$: no $ for range names
3. You can capture the calues in the Debug.print line in variables.

"AA2e72E" wrote:

> In a cell formatted as dd/mm/yyyy, type this formula:
>
> =LARGE("A1:A4",1) ' Assumes your dates are in A1:A4
>
> "ChristinaC" wrote:
>
> > Can someone help me with this one. I have a spreadsheet with a list of
> > Purchase orders that have more than one line. Eg
> >
> > Po No Line Date
> > no
> > po12345 1 18/02/2004
> > po12345 2 05/04/2005
> > po12345 3 16/02/2003
> > po12345 4 06/02/2004
> > po12346 1 09/02/2005 etc.....
> >
> > Can someone show me what code I need to use to find the latest date in the
> > third column against each po number. Hope this makes sense.

[ChristinaC]

The data can be sorted like this but the object of the exercise is to get a
list of unique purchase orders of one line with the latest date. If there is
a po with only one line then that would be unique. Hope this makes sense.

"Tom Ogilvy" wrote:

> Can the data be sorted on Po No and Date. Can a formula be put in the next
> adjacent column. Where do you want the output? I assume this is a list of
> unique Po No with corresponding latest dates?
>
> --
> Regards,
> Tom Ogilvy
>
> "ChristinaC" <[email protected]> wrote in message
> news:[email protected]...
> > Can someone help me with this one. I have a spreadsheet with a list of
> > Purchase orders that have more than one line. Eg
> >
> > Po No Line Date
> > no
> > po12345 1 18/02/2004
> > po12345 2 05/04/2005
> > po12345 3 16/02/2003
> > po12345 4 06/02/2004
> > po12346 1 09/02/2005 etc.....
> >
> > Can someone show me what code I need to use to find the latest date in the
> > third column against each po number. Hope this makes sense.
>
>
>

[Tom Ogilvy]

So sort it by Po No and date (ascending) so the last record for each Po No
will be the latest date

then in the next available column put in a formula like (assume D2 for your
example)

=if(A2<>A3,"Last","")

then drag fill down the column.

Now select A1 and do Data=>filter=>Autofilter
select the dropdown in D and select Last from the dropdown choices.

This should give you the list you need

You can select your data and copy it to a new worksheet. (Only the visible
cells will be copied).

To turn off the filter, repeat Data=>Filter=>Autofilter

This approach could be implemented in code as well.

--
Regards,
Tom Ogilvy

"ChristinaC" <[email protected]> wrote in message
news:[email protected]...
> The data can be sorted like this but the object of the exercise is to get
a
> list of unique purchase orders of one line with the latest date. If there
is
> a po with only one line then that would be unique. Hope this makes sense.
>
> "Tom Ogilvy" wrote:
>
> > Can the data be sorted on Po No and Date. Can a formula be put in the
next
> > adjacent column. Where do you want the output? I assume this is a list
of
> > unique Po No with corresponding latest dates?
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "ChristinaC" <[email protected]> wrote in message
> > news:[email protected]...
> > > Can someone help me with this one. I have a spreadsheet with a list
of
> > > Purchase orders that have more than one line. Eg
> > >
> > > Po No Line Date
> > > no
> > > po12345 1 18/02/2004
> > > po12345 2 05/04/2005
> > > po12345 3 16/02/2003
> > > po12345 4 06/02/2004
> > > po12346 1 09/02/2005 etc.....
> > >
> > > Can someone show me what code I need to use to find the latest date in
the
> > > third column against each po number. Hope this makes sense.
> >
> >
> >

[ChristinaC]

Thanks, I will try this only I am not sure how this works when you have two
different po's with the same date.

"Tom Ogilvy" wrote:

> So sort it by Po No and date (ascending) so the last record for each Po No
> will be the latest date
>
> then in the next available column put in a formula like (assume D2 for your
> example)
>
> =if(A2<>A3,"Last","")
>
> then drag fill down the column.
>
> Now select A1 and do Data=>filter=>Autofilter
> select the dropdown in D and select Last from the dropdown choices.
>
> This should give you the list you need
>
> You can select your data and copy it to a new worksheet. (Only the visible
> cells will be copied).
>
> To turn off the filter, repeat Data=>Filter=>Autofilter
>
> This approach could be implemented in code as well.
>
> --
> Regards,
> Tom Ogilvy
>
> "ChristinaC" <[email protected]> wrote in message
> news:[email protected]...
> > The data can be sorted like this but the object of the exercise is to get
> a
> > list of unique purchase orders of one line with the latest date. If there
> is
> > a po with only one line then that would be unique. Hope this makes sense.
> >
> > "Tom Ogilvy" wrote:
> >
> > > Can the data be sorted on Po No and Date. Can a formula be put in the
> next
> > > adjacent column. Where do you want the output? I assume this is a list
> of
> > > unique Po No with corresponding latest dates?
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "ChristinaC" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Can someone help me with this one. I have a spreadsheet with a list
> of
> > > > Purchase orders that have more than one line. Eg
> > > >
> > > > Po No Line Date
> > > > no
> > > > po12345 1 18/02/2004
> > > > po12345 2 05/04/2005
> > > > po12345 3 16/02/2003
> > > > po12345 4 06/02/2004
> > > > po12346 1 09/02/2005 etc.....
> > > >
> > > > Can someone show me what code I need to use to find the latest date in
> the
> > > > third column against each po number. Hope this makes sense.
> > >
> > >
> > >
>
>
>

[Tom Ogilvy]

It should work fine since your primary key is Po No. The formula doesn't
look at date at all. Since the records will be "grouped" by Po No, the last
date for each Po No should be the last record in each group. The formula
just shows which record is the last record for each group. If it doesn't
work, then we are misunderstanding what you want or what your data contains
(or you don't properly sort your data on two columns which this is
dependent on).

--
Regards,
Tom Ogilvy

"ChristinaC" <[email protected]> wrote in message
news:[email protected]...
> Thanks, I will try this only I am not sure how this works when you have
two
> different po's with the same date.
>
> "Tom Ogilvy" wrote:
>
> > So sort it by Po No and date (ascending) so the last record for each Po
No
> > will be the latest date
> >
> > then in the next available column put in a formula like (assume D2 for
your
> > example)
> >
> > =if(A2<>A3,"Last","")
> >
> > then drag fill down the column.
> >
> > Now select A1 and do Data=>filter=>Autofilter
> > select the dropdown in D and select Last from the dropdown choices.
> >
> > This should give you the list you need
> >
> > You can select your data and copy it to a new worksheet. (Only the
visible
> > cells will be copied).
> >
> > To turn off the filter, repeat Data=>Filter=>Autofilter
> >
> > This approach could be implemented in code as well.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "ChristinaC" <[email protected]> wrote in message
> > news:[email protected]...
> > > The data can be sorted like this but the object of the exercise is to
get
> > a
> > > list of unique purchase orders of one line with the latest date. If
there
> > is
> > > a po with only one line then that would be unique. Hope this makes
sense.
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > Can the data be sorted on Po No and Date. Can a formula be put in
the
> > next
> > > > adjacent column. Where do you want the output? I assume this is a
list
> > of
> > > > unique Po No with corresponding latest dates?
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > > "ChristinaC" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Can someone help me with this one. I have a spreadsheet with a
list
> > of
> > > > > Purchase orders that have more than one line. Eg
> > > > >
> > > > > Po No Line Date
> > > > > no
> > > > > po12345 1 18/02/2004
> > > > > po12345 2 05/04/2005
> > > > > po12345 3 16/02/2003
> > > > > po12345 4 06/02/2004
> > > > > po12346 1 09/02/2005 etc.....
> > > > >
> > > > > Can someone show me what code I need to use to find the latest
date in
> > the
> > > > > third column against each po number. Hope this makes sense.
> > > >
> > > >
> > > >
> >
> >
> >

[ChristinaC]

It would help if I read your instructions properly. It works and works well.
Many thanks for your assistance.

"ChristinaC" wrote:

> Thanks, I will try this only I am not sure how this works when you have two
> different po's with the same date.
>
> "Tom Ogilvy" wrote:
>
> > So sort it by Po No and date (ascending) so the last record for each Po No
> > will be the latest date
> >
> > then in the next available column put in a formula like (assume D2 for your
> > example)
> >
> > =if(A2<>A3,"Last","")
> >
> > then drag fill down the column.
> >
> > Now select A1 and do Data=>filter=>Autofilter
> > select the dropdown in D and select Last from the dropdown choices.
> >
> > This should give you the list you need
> >
> > You can select your data and copy it to a new worksheet. (Only the visible
> > cells will be copied).
> >
> > To turn off the filter, repeat Data=>Filter=>Autofilter
> >
> > This approach could be implemented in code as well.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "ChristinaC" <[email protected]> wrote in message
> > news:[email protected]...
> > > The data can be sorted like this but the object of the exercise is to get
> > a
> > > list of unique purchase orders of one line with the latest date. If there
> > is
> > > a po with only one line then that would be unique. Hope this makes sense.
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > Can the data be sorted on Po No and Date. Can a formula be put in the
> > next
> > > > adjacent column. Where do you want the output? I assume this is a list
> > of
> > > > unique Po No with corresponding latest dates?
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > > "ChristinaC" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Can someone help me with this one. I have a spreadsheet with a list
> > of
> > > > > Purchase orders that have more than one line. Eg
> > > > >
> > > > > Po No Line Date
> > > > > no
> > > > > po12345 1 18/02/2004
> > > > > po12345 2 05/04/2005
> > > > > po12345 3 16/02/2003
> > > > > po12345 4 06/02/2004
> > > > > po12346 1 09/02/2005 etc.....
> > > > >
> > > > > Can someone show me what code I need to use to find the latest date in
> > the
> > > > > third column against each po number. Hope this makes sense.
> > > >
> > > >
> > > >
> >
> >
> >