Sign in to follow this  
Followers 0
PACaleala

Save only the oldest records (Excel)

6 posts in this topic

#1 ·  Posted (edited)

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:

;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 by PACaleala

Share this post


Link to post
Share on other sites



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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 ?

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

thanks water

Share this post


Link to post
Share on other sites

In this case you simply could do a numeric comparison.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0