Jump to content

Logic for looping through unique data


Recommended Posts

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

 

#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

 

Link to post
Share on other sites
  • Moderators

@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,?

"Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball

How to get your question answered on this forum!

Link to post
Share on other sites

... 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)

 

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Link to post
Share on other sites
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)

 

Code hard, but don’t hard code...

Link to post
Share on other sites

@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]

 

Link to post
Share on other sites
Posted (edited)

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 by mikell
sorting
Link to post
Share on other sites
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 :P ...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.

#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)

 

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Link to post
Share on other sites
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!

Code hard, but don’t hard code...

Link to post
Share on other sites
8 hours ago, Chimp said:

... there are also other pedissequo :P ...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 :D

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Link to post
Share on other sites

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

#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

 

Click here to see my signature:

Spoiler

ALWAYS GOOD TO READ:

 

Link to post
Share on other sites
Posted (edited)

@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.

 

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 by zuladabef
Link to post
Share on other sites
Posted (edited)
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 : :P

#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 by mikell
tiny code
Link to post
Share on other sites
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

#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 ;)

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

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
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...