AndreasNWWWWW Posted February 9, 2022 Posted February 9, 2022 (edited) Good evening! i was just curious, is it possible for autoit to consolidate equal items in one row, and sum the corresponding line in the other? i'm making a timetracker tool and with some help from Nine i have gotten it to work, however i'm trying to figure out how to consolidate data. like everything with projectnumber 2021-033-001 is merged to a total time instead of different lines so in the example below it would be three lines instead of 4,and line 2021-033-001 would count 2 seconds instead of two times one second Edited February 9, 2022 by AndreasNWWWWW
Nine Posted February 9, 2022 Posted February 9, 2022 After reading the sheet, you could use _ArrayUnique to eliminate redondant rows... “They did not know it was impossible, so they did it” ― Mark Twain Spoiler Block all input without UAC Save/Retrieve Images to/from Text Monitor Management (VCP commands) Tool to search in text (au3) files Date Range Picker Virtual Desktop Manager Sudoku Game 2020 Overlapped Named Pipe IPC HotString 2.0 - Hot keys with string x64 Bitwise Operations Multi-keyboards HotKeySet Recursive Array Display Fast and simple WCD IPC Multiple Folders Selector Printer Manager GIF Animation (cached) Screen Scraping Multi-Threading Made Easy
AndreasNWWWWW Posted February 9, 2022 Author Posted February 9, 2022 8 minutes ago, Nine said: After reading the sheet, you could use _ArrayUnique to eliminate redondant rows... but does that not just delete duplicate values? and not merge them togheter? ref the helptext Quote Returns the Elements from a column of a 1D or 2D array, removing all duplicates
Nine Posted February 9, 2022 Posted February 9, 2022 (edited) If I understand you correctly, you want to modify the spreadsheet by eliminating all duplicates row (unless merging has another meaning for you ?) Edit : I think I got it. You want to modify the spreadsheet and add second column together for all duplicate rows (in first column). Edited February 9, 2022 by Nine AndreasNWWWWW 1 “They did not know it was impossible, so they did it” ― Mark Twain Spoiler Block all input without UAC Save/Retrieve Images to/from Text Monitor Management (VCP commands) Tool to search in text (au3) files Date Range Picker Virtual Desktop Manager Sudoku Game 2020 Overlapped Named Pipe IPC HotString 2.0 - Hot keys with string x64 Bitwise Operations Multi-keyboards HotKeySet Recursive Array Display Fast and simple WCD IPC Multiple Folders Selector Printer Manager GIF Animation (cached) Screen Scraping Multi-Threading Made Easy
AndreasNWWWWW Posted February 9, 2022 Author Posted February 9, 2022 (edited) 16 minutes ago, Nine said: If I understand you correctly, you want to modify the spreadsheet by eliminating all duplicates row (unless merging has another meaning for you ?) Yes, and no. i want to delete duplicates, but i want the total time of those duplicates to be added to one value. ie. projectnumber 1 has 30 minutes recorded time projectnumber 1(duplicate) has 1 hour recorded time. i want to merge those two into one. projectnumber 1 = 1 hour 30 minutes. like this, only this merging happens in cell A and B Edited February 9, 2022 by AndreasNWWWWW
Nine Posted February 9, 2022 Posted February 9, 2022 Here one way to do it : #include <Excel.au3> Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Total.xls") Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.UsedRange) _ArraySort($aResult, 0, 1, 0, 0) Local $aFinal[UBound($aResult)][2] = [[$aResult[0][0], $aResult[0][1]]] _ArrayDisplay($aResult) Local $iIn = 1, $iOut = 1 While $iIn < UBound($aResult) $aFinal[$iOut][0] = $aResult[$iIn][0] $aFinal[$iOut][1] = $aResult[$iIn][1] $iIn += 1 If $iIn = UBound($aResult) Then ExitLoop While $aResult[$iIn][0] = $aFinal[$iOut][0] $aFinal[$iOut][1] = AddTime($aResult[$iIn][1], $aFinal[$iOut][1]) $iIn += 1 If $iIn = UBound($aResult) Then ExitLoop 2 WEnd $iOut += 1 WEnd ReDim $aFinal[$iOut+1][2] _ArrayDisplay($aFinal) _Excel_SheetAdd($oWorkbook, $oWorkbook.ActiveSheet, False, 1, "Merged") _Excel_RangeWrite($oWorkbook, "Merged", $aFinal) _Excel_BookSave($oWorkbook) _Excel_Close($oExcel) I will let you do the AddTime function...Mine just + the 2 values “They did not know it was impossible, so they did it” ― Mark Twain Spoiler Block all input without UAC Save/Retrieve Images to/from Text Monitor Management (VCP commands) Tool to search in text (au3) files Date Range Picker Virtual Desktop Manager Sudoku Game 2020 Overlapped Named Pipe IPC HotString 2.0 - Hot keys with string x64 Bitwise Operations Multi-keyboards HotKeySet Recursive Array Display Fast and simple WCD IPC Multiple Folders Selector Printer Manager GIF Animation (cached) Screen Scraping Multi-Threading Made Easy
junkew Posted February 9, 2022 Posted February 9, 2022 If you have it already in excel its just a matter of creating a pivottable Select your data Insert pivot table sum the field values FAQ 31 How to click some elements, FAQ 40 Test automation with AutoIt, Multithreading CLR .NET Powershell CMDLets
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