Latest date

[Guest]

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?

 

[Guest]

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

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

 

[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)

 

[Guest]

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.

 

[Guest]

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]

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.

 

[Guest]

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

 

[Guest]

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

 

[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).