PACaleala Posted January 16, 2015 Share Posted January 16, 2015 (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: 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 Link to comment Share on other sites More sharing options...
water Posted January 16, 2015 Share Posted January 16, 2015 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
PACaleala Posted January 17, 2015 Author Share Posted January 17, 2015 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 ? Link to comment Share on other sites More sharing options...
water Posted January 17, 2015 Share Posted January 17, 2015 (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 January 17, 2015 by water My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
PACaleala Posted January 19, 2015 Author Share Posted January 19, 2015 The date in excel is "17/11/2014" , the arrayDisplay shows "20141117000000". thanks water Link to comment Share on other sites More sharing options...
water Posted January 19, 2015 Share Posted January 19, 2015 In this case you simply could do a numeric comparison. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now