Logic for looping through unique data

[zuladabef]

I have an Excel spreadsheet with Names and Time of Day (for setting appointments).  Names are unique data, but time of day is not.  So, anywhere from 1-10 people could be grouped and have the same appointment time.  I need to find all unique times of day, then based upon those times of day, create an appointments.  Basically, I want to group all the appointments of a particular time of day and then run a loop on one time of day, at a time.  I hope that makes some sense.

Thanks for your help!

Data looks something like this:

Name | Time of Appointment

Marky | 07:30
Ricky | 07:30
Danny | 08:00
Terry | 08:15
Mikey | 08:15
Davey | 08:15
Timmy | 08:30
Tommy | 08:30
Joey | 08:30
Robby | 08:30
Johnny | 13:30
Brian | 14:30

 

expandcollapse popup

#include <Excel.au3>
#include <Array.au3>

Local $sFilePathExcel = @ScriptDir & "\excel files\AppointmentTimes.xlsx"
Local $aResultFromExcelFile = _GetDataFromExcelFile($sFilePathExcel)

;~ _ArrayDisplay($aResultFromExcelFile)

For $iCC = 1 To UBound($aResultFromExcelFile) - 1
    ;Group times of day together
    ;Loop through the groups of appointment times, but only one group of time together (like 07:30, 8:30, etc)
    ;Do stuff to make the appointment times
    ConsoleWrite($aResultFromExcelFile[$iCC][1] & @TAB & $aResultFromExcelFile[$iCC][3] & @CRLF)
Next



Func _GetDataFromExcelFile($sFilePathExcel)

;~  Local $sFileOpen

    ; Create application object and open an example workbook
    Local $oExcel = _Excel_Open()
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    Local $oWorkbook = _Excel_BookOpen($oExcel, $sFilePathExcel)
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & $sFilePathExcel & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
    EndIf

    ; Read data from a single cell on the active sheet of the specified workbook
    Local $aResult = _Excel_RangeRead($oWorkbook, Default, Default)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

    Return $aResult

EndFunc   ;==>_GetDataFromExcelFile

 

[JLogan3o13]

@zuladabef can you please clarify a bit? Using the 7:30 appointment time, are you saying you want to pick just the first person with that time, and schedule the appointment for that time? Or you would grab both people at 7:30 and schedule them for the same time, all three at 8:15 and schedule them for the same time, etc,?

[zuladabef]

Thank you for that question!  I want to grab ALL people at 07:30 and schedule them at the same time.  The same for all the other times as well.  Thanks!

[JockoDundee]

Are the times always in ascending order, as per your example?

[Gianni]

... quite easy using some _array* functions
here an example that will group persons by setting the same group number in the first column of the outgoing array.
there is non need to have times sorted in input, they can be random:

#include <array.au3>
Local $aResultFromExcelFile[][] = _
        [['Marky', '07:30'], _
        ['Ricky', '07:30'], _
        ['Danny', '08:00'], _
        ['Terry', '08:15'], _
        ['Mikey', '08:15'], _
        ['Davey', '08:15'], _
        ['Timmy', '08:30'], _
        ['Tommy', '08:30'], _
        ['Joey', '08:30'], _
        ['Robby', '08:30'], _
        ['Johnny', '13:30'], _
        ['Brian', '14:30']]

Local $aGroups = _ArrayUnique($aResultFromExcelFile, 1)    ; find unique Times
Local $aOut[UBound($aResultFromExcelFile)][3] ; create a new array with a new column "Group"
Local $iGroup = 0, $iIndex = 0

For $i = 1 To $aGroups[0]
    $aSameTime = _ArrayFindAll($aResultFromExcelFile, $aGroups[$i], 0, 0, 0, 0, 1) ; find all indices with same time
    For $iSameTime = 0 To UBound($aSameTime) - 1
        $aOut[$iIndex][0] = $iGroup
        $aOut[$iIndex][1] = $aResultFromExcelFile[$aSameTime[$iSameTime]][0]
        $aOut[$iIndex][2] = $aResultFromExcelFile[$aSameTime[$iSameTime]][1]
        $iIndex += 1
    Next
    $iGroup += 1
Next

_ArrayDisplay($aOut)

 

[JockoDundee]

3 hours ago, Chimp said:

there is non need to have times sorted in input

nice work.

The reason why I asked though, is not that it can't be done if random, but only that it was already sorted and its slightly simpler if it comes sorted:

#include <array.au3>
Local $aResultFromExcelFile[][] = _
        [['Marky' , '07:30', 0], _
        [ 'Ricky' , '07:30', 0], _
        [ 'Danny' , '08:00', 0], _
        [ 'Terry' , '08:15', 0], _
        [ 'Mikey' , '08:15', 0], _
        [ 'Davey' , '08:15', 0], _
        [ 'Timmy' , '08:30', 0], _
        [ 'Tommy' , '08:30', 0], _
        [ 'Joey'  , '08:30', 0], _
        [ 'Robby' , '08:30', 0], _
        [ 'Johnny', '13:30', 0], _
        [ 'Brian' , '14:30', 0]]

For $n = 1 To Ubound($aResultFromExcelFile) - 1
   If  $aResultFromExcelFile[$n][1] = $aResultFromExcelFile[$n-1][1] Then
       $aResultFromExcelFile[$n][2] = $aResultFromExcelFile[$n-1][2]
   Else
       $aResultFromExcelFile[$n][2] = $aResultFromExcelFile[$n-1][2]+1
   EndIf
Next

_ArrayDisplay($aResultFromExcelFile)

 

[zuladabef]

@Chimp Can you tell me a bit more of what is going on in these two lines?  I am not familiar with this technique.  Thanks!

 

$aOut[$iIndex][1] = $aResultFromExcelFile[$aSameTime[$iSameTime]][0]
$aOut[$iIndex][2] = $aResultFromExcelFile[$aSameTime[$iSameTime]][1]

 

[mikell]

My 2 cents

#include <array.au3>

Local $aResultFromExcelFile[][] = _
        [['Marky' , '07:30'], _
        [ 'Ricky' , '07:30'], _
        [ 'Danny' , '08:00'], _
        [ 'Terry' , '08:15'], _
        [ 'Mikey' , '08:15'], _
        [ 'Davey' , '08:15'], _
        [ 'Timmy' , '08:30'], _
        [ 'Tommy' , '08:30'], _
        [ 'Joey'  , '08:30'], _
        [ 'Robby' , '08:30'], _
        [ 'Johnny', '13:30'], _
        [ 'Brian' , '14:30']]
; _ArraySort($aResultFromExcelFile, 0, 0, 0, 1) ; if times are unsorted

Local $i = 0, $u = Ubound($aResultFromExcelFile)
Local $res[$u][2]
$res[0][0] = $aResultFromExcelFile[0][1]
$res[0][1] = $aResultFromExcelFile[0][0]

For $n = 1 To $u - 1
   If  $aResultFromExcelFile[$n][1] = $aResultFromExcelFile[$n-1][1] Then
    $res[$i][1] &= ", " & $aResultFromExcelFile[$n][0]
   Else
    $i += 1
    $res[$i][0] = $aResultFromExcelFile[$n][1]
    $res[$i][1] =  $aResultFromExcelFile[$n][0]
   EndIf
Next
ReDim $res[$i+1][2]

_ArrayDisplay($res)

 

Edited May 14, 2021 by mikell
sorting

[Gianni]

14 hours ago, zuladabef said:

@Chimp Can you tell me a bit more of what is going on in these two lines?  I am not familiar with this technique.  Thanks!

 

$aOut[$iIndex][1] = $aResultFromExcelFile[$aSameTime[$iSameTime]][0]
$aOut[$iIndex][2] = $aResultFromExcelFile[$aSameTime[$iSameTime]][1]

 

... as you can see from other posts, there are also other pedissequo ...simpler ways to achieve the same result,
anyway, if you are interested on how to use that array functions, I've added some comments to the list.

expandcollapse popup

#include <array.au3>
Local $aResultFromExcelFile[][] = _
        [['Marky', '07:30'], _
        ['Ricky', '07:30'], _
        ['Danny', '08:00'], _
        ['Terry', '08:15'], _
        ['Mikey', '08:15'], _
        ['Davey', '08:15'], _
        ['Timmy', '08:30'], _
        ['Tommy', '08:30'], _
        ['Joey', '08:30'], _
        ['Robby', '08:30'], _
        ['Johnny', '13:30'], _
        ['Brian', '14:30']]

; the $aGroups array will contain all the different times repeated only one time
; i.e. the 08:30 will appear only one time in this array even if there are more.
; so we know how many groups we need (one group for each different hour)
Local $aGroups = _ArrayUnique($aResultFromExcelFile, 1) ; find unique Times
; _ArrayDisplay($aGroups) ; debug

; we create a new array that will contain all our meetings groupped by same time
; this array must have the same number of rows as the $aResultFromExcelFile array
; and one more column to hold the group number with the same time, 3 columns instead of 2
; The $aOut array:
;     0   1   2
;   +---+---+---+
;   |   |   |   |
;   +---+---+---+
;   |   |   |   |
;     ^   ^   ^
;     |   |   |
;     |   |   +-- column 2   ...][2] will contain the time
;     |   |
;     |   +------ column 1   ...][1] will contain the name
;     |
;     +---------- column 0   ...][0] will contain the number of the group
;
Local $aOut[UBound($aResultFromExcelFile)][3] ; create a new array with a new column "Group"

; 2 variables used as pointers
Local $iGroup = 0, $iIndex = 0

; here we loop for each time group
; element $aGroups[0] contains the number of elements contained in the array
For $i = 1 To $aGroups[0]
    ;   the _ArrayFindAll() function is a nice function
    ;   it will return you the indices (row numbers) where are located
    ;   the elements that you are looking for into the $aResultFromExcelFile array
    $aSameTime = _ArrayFindAll($aResultFromExcelFile, $aGroups[$i], 0, 0, 0, 0, 1) ; find all indices (rows) with same time
    ;   _ArrayDisplay($aSameTime) ; debug

    ;   in this loop we get all the rows with the same time from the $aResultFromExcelFile array
    ;   and copy them to our final array
    ;   group number in column [0]
    ;   name         in column [1]
    ;   time         in column [2]
    For $iSameTime = 0 To UBound($aSameTime) - 1 ; scan all persons with the same time
        $aOut[$iIndex][0] = $iGroup
        $aOut[$iIndex][1] = $aResultFromExcelFile[$aSameTime[$iSameTime]][0] ; column 0 contains the name
        $aOut[$iIndex][2] = $aResultFromExcelFile[$aSameTime[$iSameTime]][1] ; column 1 contains the time
;       -----------------   --------------------- ---------------------
;           ^                   ^                     ^
;           |                   |                     |   row where is the next person
;           |                   |                     +-- with the same time
;           |                   |
;           |                   |                         we read the data from the array
;           |                   +------------------------ $aResultFromExcelFile
;           |
;           +-------------------------------------------- we populate the 3 columns of the output

        $iIndex += 1 ; we pass to the next person with the same time
    Next
    $iGroup += 1 ; we pass to the next group
Next

_ArrayDisplay($aOut)

 

[JockoDundee]

5 hours ago, Chimp said:

there are also other pedissequo

Sure, this is fair swipe at my staid work.

But no such criticism can be lodged against @mikell‘s creation.

Take a look at the self-mulching action of the $res array!

Capped off with the delightfully unexpected, if trenchant truncation:

ReDim $res[$i+1][2]

Bravissimi to you both!

[Gianni]

8 hours ago, Chimp said:

... there are also other pedissequo ...simpler ways to achieve the same result,

p.s.
I respect and try to learn from any form of coding, from anyone it comes from.
I just wanted to "tease" a little with a little humor and with no intention of offending anyone, just a little healthy humor and possibly even stimulate some creative antagonism.
I find satire amusing in general, without problems even if and when it is directed toward me

3 hours ago, JockoDundee said:

Bravissimi to you both!

.. grazie

[FrancescoDiMuro]

Strange that a cat like @mikell didn't end up with one of his SRE(R)? and/or Scripting Dictionary solutions

expandcollapse popup

#include <Array.au3>

Test()

Func Test()

    Local $arrResultFromExcelFile[][] = _
          [['Marky' , '07:30'], _
          [ 'Ricky' , '07:30'], _
          [ 'Danny' , '08:00'], _
          [ 'Terry' , '08:15'], _
          [ 'Mikey' , '08:15'], _
          [ 'Davey' , '08:15'], _
          [ 'Timmy' , '08:30'], _
          [ 'Tommy' , '08:30'], _
          [ 'Joey'  , '08:30'], _
          [ 'Robby' , '08:30'], _
          [ 'Johnny', '13:30'], _
          [ 'Brian' , '14:30']], _
          $objDictionary, _
          $arrDictionaryItems, _
          $arrDictionaryKeys, _
          $strKey, _
          $varItem, _
          $arrAppointments[0][2]

    ; Create Dictionary object
    $objDictionary = ObjCreate("Scripting.Dictionary")

    ; Loop through the array
    For $i = 0 To UBound($arrResultFromExcelFile) - 1 Step 1

        ; Get the key and item
        $strKey = $arrResultFromExcelFile[$i][1]
        $varItem = $arrResultFromExcelFile[$i][0]

        ; If the key already exists, then add a comma and concatenate the new item with the existing one(s)
        If $objDictionary.Exists($strKey) Then
            $objDictionary.Item($strKey) &= "," & $varItem
        Else
            $objDictionary.Add($strKey, $varItem)
        EndIf

    Next

    ; Get the Dictionary items
    $arrDictionaryItems = $objDictionary.Items

    ; Display the Dictionary items
    ; _ArrayDisplay($arrDictionaryItems)

    ; Get the Dictionary keys
    $arrDictionaryKeys = $objDictionary.Keys

    ; Merge the two 1D arrays in a 2D array
    _ArrayMerge($arrAppointments, $arrDictionaryKeys, $arrDictionaryItems)

    _ArrayDisplay($arrAppointments)

EndFunc

Func _ArrayMerge(ByRef $arrDestArray, $arrArray1, $arrArray2)

    For $i = 0 To UBound($arrArray1) - 1 Step 1
        _ArrayAdd($arrDestArray, $arrArray1[$i] & "|" & $arrArray2[$i])
    Next

EndFunc

 

[JockoDundee]

1 hour ago, FrancescoDiMuro said:

Strange that a cat like @mikell didn't end up with one of his SRE(R)?

Are you saying you can’t teach an old cat new tricks?

[FrancescoDiMuro]

@JockoDundee
Never thought that, but since mikell is a fan of those two approaches for a lot of things, why he didn't try to use them in this situation?

[JockoDundee]

40 minutes ago, FrancescoDiMuro said:

why he didn't try to use them in this situation?

It appears that @mikell may have been going for the classic “one-pager”

[zuladabef]

@Chimp This is fantastic, thanks for the commentary!

*Edited: forgot to add Excel file

I am running into a new issue now that the $aResultFromExcelFile is coming from an actual Excel file, and not our typed out array.  It is throwing

Quote

6 - $vValue was not found in array

Any idea where the issue is?  I have a suspicion that it has something to do with data types, but I haven't been able to confirm that.

 

expandcollapse popup

Local $sFilePathExcel = @ScriptDir & "\Data For Appointments.xlsx"

; Create application object and open an example workbook
    Local $oExcel = _Excel_Open()
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    Local $oWorkbook = _Excel_BookOpen($oExcel, $sFilePathExcel)
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & $sFilePathExcel & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
    EndIf

    ; Read data from the active sheet of the specified workbook
    Local $aResult = _Excel_RangeRead($oWorkbook, Default, Default)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

    
    
    ; the $aGroups array will contain all the different times repeated only one time
; i.e. the 08:30 will appear only one time in this array even if there are more.
; so we know how many groups we need (one group for each different hour)
Local $aGroups = _ArrayUnique($aResultFromExcelFile, 3) ; find unique Times
; _ArrayDisplay($aGroups) ; debug
 _ArrayDisplay($aResultFromExcelFile) ; debug
 _ArrayDisplay($aGroups) ; debug
Local $aOut[UBound($aResultFromExcelFile)][4] ; create a new array with a new column "Group"
; 2 variables used as pointers
Local $iGroup = 0, $iIndex = 0
#cs

; we create a new array that will contain all our meetings groupped by same time
; this array must have the same number of rows as the $aResultFromExcelFile array
; and one more column to hold the group number with the same time, 3 columns instead of 2
; The $aOut array:
;     0   1   2
;   +---+---+---+
;   |   |   |   |
;   +---+---+---+
;   |   |   |   |
;     ^   ^   ^
;     |   |   |
;     |   |   +-- column 2   ...][2] will contain the time
;     |   |
;     |   +------ column 1   ...][1] will contain the name
;     |
;     +---------- column 0   ...][0] will contain the number of the group
;
#ce

; here we loop for each time group
; element $aGroups[0] contains the number of elements contained in the array
For $i = 1 To $aGroups[0]
    ;   the _ArrayFindAll() function is a nice function
    ;   it will return you the indices (row numbers) where are located
    ;   the elements that you are looking for into the $aResultFromExcelFile array
    $aSameTime = _ArrayFindAll($aResultFromExcelFile, $aGroups[$i], 0, 0, 0, 0, 1) ; find all indices (rows) with same time
    ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $aGroups[$i] = ' & $aGroups[$i] & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console
;~  $aSameTime = _ArrayFindAll($aResultFromExcelFile, $aGroups[$i], 0, 0, 0, 0, 1) ; find all indices (rows) with same time
    ;   _ArrayDisplay($aSameTime) ; debug
    _ArrayDisplay($aSameTime) ; debug

    ;   in this loop we get all the rows with the same time from the $aResultFromExcelFile array
    ;   and copy them to our final array
    ;   group number in column [0]
    ;   name         in column [1]
    ;   time         in column [2]
    For $iSameTime = 0 To UBound($aSameTime) - 1 ; scan all persons with the same time
        ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $iSameTime = ' & $iSameTime & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console
        $aOut[$iIndex][0] = $iGroup
        $aOut[$iIndex][1] = $aResultFromExcelFile[$aSameTime[$iSameTime]][0] ; column 0 contains the name
        $aOut[$iIndex][2] = $aResultFromExcelFile[$aSameTime[$iSameTime]][1] ; column 1 contains the time
        $aOut[$iIndex][3] = $aResultFromExcelFile[$aSameTime[$iSameTime]][3] ; column 1 contains the time
        ;       -----------------   --------------------- ---------------------
        ;           ^                   ^                     ^
        ;           |                   |                     |   row where is the next person
        ;           |                   |                     +-- with the same time
        ;           |                   |
        ;           |                   |                         we read the data from the array
        ;           |                   +------------------------ $aResultFromExcelFile
        ;           |
        ;           +-------------------------------------------- we populate the 3 columns of the output

        $iIndex += 1 ; we pass to the next person with the same time
    Next
    $iGroup += 1 ; we pass to the next group
Next

_ArrayDisplay($aOut)

 

Data For Appointments.xlsx

Edited May 17, 2021 by zuladabef

[JockoDundee]

2 minutes ago, zuladabef said:

@Chimp This is fantastic, thanks for the commentary!

Yes it is!
I’m embarrassed that I didn’t upvote 💟 it ‘til just now!

[mikell]

10 hours ago, FrancescoDiMuro said:

why he didn't try to use them in this situation?

I like salt on mice but not in coffee

 

Edit :

#include <Array.au3>
#include <Excel.au3>

Local $sFilePathExcel = @ScriptDir & "\Data For Appointments.xlsx"
Local $oExcel = _Excel_Open(false)
Local $oWorkbook = _Excel_BookOpen($oExcel, $sFilePathExcel)
Local $aResult = _Excel_RangeRead($oWorkbook, Default, Default)
_ArrayColDelete($aResult, 1)
_ArrayColDelete($aResult, 1) ; it is NOT stuttering 
_ArraySwap($aResult, 0, 1, true)

Local $i = 0, $u = Ubound($aResult)
Local $res[$u][2]
$res[0][0] = $aResult[0][0]
$res[0][1] = $aResult[0][1]

For $n = 1 To $u - 1
   If  $aResult[$n][0] = $aResult[$n-1][0] Then
    $res[$i][1] &= ", " & $aResult[$n][1]
   Else
    $i += 1
    $res[$i][0] = $aResult[$n][0]
    $res[$i][1] =  $aResult[$n][1]
   EndIf
Next
ReDim $res[$i+1][2]

_ArrayDisplay($res)

BTW the number of the 'group' is the index of the row

Edited May 17, 2021 by mikell
tiny code

[Gianni]

2 hours ago, zuladabef said:

Any idea where the issue is?

mainly, since your real data has 4 columns, you have to also adjust the seventh parameter of the _ArrayFindAll function so to search in column 4 instead of column 2

expandcollapse popup

#include <Array.au3>
#include <Excel.au3>
Local $sFilePathExcel = @ScriptDir & "\Data For Appointments.xlsx"

; Create application object and open an example workbook
    Local $oExcel = _Excel_Open()
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    Local $oWorkbook = _Excel_BookOpen($oExcel, $sFilePathExcel)
    If @error Then
        MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & $sFilePathExcel & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        _Excel_Close($oExcel)
        Exit
    EndIf

    ; Read data from the active sheet of the specified workbook
    Local $aResultFromExcelFile = _Excel_RangeRead($oWorkbook, Default, Default)

    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_Close($oExcel)
; _ArrayDisplay($aResultFromExcelFile) ; debug


    ; the $aGroups array will contain all the different times repeated only one time
    ; i.e. the 08:30 will appear only one time in this array even if there are more.
    ; so we know how many groups we need (one group for each different hour)
Local $aGroups = _ArrayUnique($aResultFromExcelFile, 3) ; find unique Times
; _ArrayDisplay($aGroups) ; debug
 _ArrayDisplay($aResultFromExcelFile) ; debug
 _ArrayDisplay($aGroups) ; debug
Local $aOut[UBound($aResultFromExcelFile)][5] ; create a new array with a new column "Group"
; 2 variables used as pointers
Local $iGroup = 0, $iIndex = 0
#cs

; we create a new array that will contain all our meetings groupped by same time
; this array must have the same number of rows as the $aResultFromExcelFile array
; and one more column to hold the group number with the same time, 3 columns instead of 2
; The $aOut array:
;     0   1   2   3   4
;   +---+---+---+---+---+
;   |   |   |   |   |   |
;   +---+---+---+---+---+
;   |   |   |   |
;     ^   ^   ^
;     |   |   |
;     |   |   +-- column 2   ...][2] will contain the time
;     |   |
;     |   +------ column 1   ...][1] will contain the name
;     |
;     +---------- column 0   ...][0] will contain the number of the group
;
#ce

; here we loop for each time group
; element $aGroups[0] contains the number of elements contained in the array
For $i = 1 To $aGroups[0]
    ;   the _ArrayFindAll() function is a nice function
    ;   it will return you the indices (row numbers) where are located
    ;   the elements that you are looking for into the $aResultFromExcelFile array IN COLUMN SPECIFIED BY 7° PARAMETER
    $aSameTime = _ArrayFindAll($aResultFromExcelFile, $aGroups[$i], 0, 0, 0, 0, 3) ; find all indices (rows) with same time IN COLUMN 4
    ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $aGroups[$i] = ' & $aGroups[$i] & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console
;~  $aSameTime = _ArrayFindAll($aResultFromExcelFile, $aGroups[$i], 0, 0, 0, 0, 1) ; find all indices (rows) with same time
    ;   _ArrayDisplay($aSameTime) ; debug
    _ArrayDisplay($aSameTime, "Same Time") ; debug

    ;   in this loop we get all the rows with the same time from the $aResultFromExcelFile array
    ;   and copy them to our final array
    ;   group number in column [0]
    ;   name         in column [1]
    ;   placeholder1 in column [2]
    ;   placeholder1 in column [3]
    ;   time         in column [4]
    For $iSameTime = 0 To UBound($aSameTime) - 1 ; scan all persons with the same time
        ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $iSameTime = ' & $iSameTime & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console
        $aOut[$iIndex][0] = $iGroup
        $aOut[$iIndex][1] = $aResultFromExcelFile[$aSameTime[$iSameTime]][0] ; column 0 contains the name
        $aOut[$iIndex][2] = $aResultFromExcelFile[$aSameTime[$iSameTime]][1] ; column 1 contains the placeholder1
        $aOut[$iIndex][3] = $aResultFromExcelFile[$aSameTime[$iSameTime]][2] ; column 2 contains the placeholder2
        $aOut[$iIndex][4] = $aResultFromExcelFile[$aSameTime[$iSameTime]][3] ; column 3 contains the time
;       -----------------   --------------------- ---------------------
;           ^                   ^                     ^
;           |                   |                     |   row where is the next person
;           |                   |                     +-- with the same time
;           |                   |
;           |                   |                         we read the data from the array
;           |                   +------------------------ $aResultFromExcelFile
;           |
;           +-------------------------------------------- we populate the 3 columns of the output

        $iIndex += 1 ; we pass to the next person with the same time
    Next
    $iGroup += 1 ; we pass to the next group
Next

_ArrayDisplay($aOut)

 

2 hours ago, zuladabef said:

.... thanks for the commentary

You are welcome