Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Latest date
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Latest date
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Latest date
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Latest date
=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" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Latest date
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Latest date
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" wrote in message ... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Latest date
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" wrote in message ... 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" wrote in message ... 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Latest date
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" wrote in message ... 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" wrote in message ... 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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Latest date
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" wrote in message ... 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" wrote in message ... 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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Latest date
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Latest Taxation Books available at jain book depot LATEST BOOKRELEASES | Excel Worksheet Functions | |||
Filter latest date from multiple date entries | Excel Worksheet Functions | |||
X axis date - display beyond latest date. | Charts and Charting in Excel | |||
Need help to find a date (latest date) from a column | Excel Worksheet Functions | |||
Need help to find a date (latest date) from a column | Excel Worksheet Functions |