Excel merging items with same "name" in one colum

[AndreasNWWWWW]

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]

After reading the sheet, you could use _ArrayUnique to eliminate redondant rows...

[AndreasNWWWWW]

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]

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]

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]

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 

[junkew]

If you have it already in excel its just a matter of creating a pivottable

1. Select your data
2. Insert pivot table
3. sum the field values