Save only the oldest records (Excel)

[PACaleala]

Hello

I need to copy the records with the oldest dates from an Excel file sorted on part number(col 1) .

(column 2 is for Barcodes, column 3 is for dates).

Tried to pair records with identical data in columns 1 and 2 and then compare the dates in column 3

and display the dates  but  I do not know how to continue.

I guess my approach is wrong.

Can anyone give me a pointer on how to solve this problem ?

Here is my code:

expandcollapse popup

;001.42 F621    31259009421814  17/11/2014
;001.42 F621    31259009421814  24/11/2014
;001.42 F621    31259009421814  8/12/2014
;001.42 F621    31259009421814  15/12/2014
;001.42 L484    31259010904766  1/11/2014
;001.42 L484    31259010904766  2/11/2014
;001.42 L484    31259010904766  18/12/2014
;001.42 L484    31259010904766  15/12/2014
;003 T128   31259010701709  7/11/2014
;
#Include <Array.au3>
#include <Excel.au3>
Global $aArray, $oExcel
Global $ok, $r, $i
$oExcel = _ExcelBookOpen(@ScriptDir & "Smallsample.xlsx",1)
If @error = 1 Then
    MsgBox(0, "Error!", "Unable to use Excel !")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "Smallsample.xlsx does not exist!")
    Exit
EndIf
$aArray = _ExcelReadSheetToArray($oExcel)
_ExcelBookClose($oExcel)
Sleep(444)
For $r=1 to UBound($aArray,1) -1
;   for $i=2 to UBound($aArray,1)-1
        If $aArray[$r][1] = $aArray[$r+1][1] Then
            If $aArray[$r][2] = $aArray[$r+1][2] Then
                If $aArray[$r][3] < $aArray[$r+1][3] Then
                    MsgBox(0,"",$aArray[$r][3])
                EndIf
            EndIf
        EndIf
;   Next
Next
_ArrayDisplay($aArray, "Array using Default Parameters")
Exit
;

Intended output:

001.42 F621    31259009421814    17/11/2014

001.42 L484    31259010904766    1/11/2014
003 T128    31259010701709    7/11/2014

Edited January 16, 2015 by PACaleala

[water]

Which results do you get? Do you get the correct record? Is just the date invalid?

[PACaleala]

Hello water

I can get the correct record and data is valid.

I do not know how to use the dates in column 3 to delete some records.

For example the first four records have identical data in column 1 and 2 but the date in column 3 is always different.

At this stage I should find the minimum value for the first group of 4 records (and save record 1)

then delete the ones with newer dates (record 2,3 and 4).

Continue with the rest of the file.

Any idea on how I could do it ?

[water]

Is the date stored as a string or as date in Excel?

Means: Does _Excel_RangeRead return "17/11/2014" or 3287901 (just an example, no real data)?

Edited January 17, 2015 by water

[PACaleala]

The date in excel is "17/11/2014" , the arrayDisplay shows "20141117000000".

thanks water

[water]

In this case you simply could do a numeric comparison.