excel table with arrary and for next loop for scheduling problems

[Peggy]

[Rule]
1. A start would be to get this excel spreadsheet, people called the group, a total divided into two groups: Mary, Sam

2. Every person eating a fruit, but each times can not eat the same fruit 
3. Last eaten fruit can not eat
4. so be scheduled

Picture:
1. The first time Mary eat Apple, Sam  can not eat Apple, so Sam chose to eat tomato

 

2. The second Mary eat Banana, Sam choose to eat Apple

 
3. Third Mary eat tomato, Sam for the first time has been eaten tomato, so Sam chose to eat Strawberry

 

4. Fourth Mary eat watermelon, Sam all eat so do not choose

 

5. Fifth Mary eat Strawberry, Sam all eat so do not choose

 

Currently only think of using arrary and for next loop to write it, but I do not know to write Detailing, want to please help me, thank you very much !!!! Thank you!

Edited October 27, 2015 by markwu

[mLipok]

start with this:

_Excel_Open()

 

[Melba23]

peggy,

I cannot help with the Excel bit, but here is some logic to help you with the scheduling part:

expandcollapse popup

#include <Array.au3>

Global $aArray[8][7]

; Arrays of group and choices
Global $aMaryChoice[] = ["Mary", "Apple", "Banana", "Tomato", "Watermelon", "Strawberry"]
Global $aSamChoice[] = ["Sam", "Apple", "Tomato", "Strawberry"]

; Fill array with choices and group
For $i = 0 To UBound($aMaryChoice) - 2
    $aArray[$i][0] = $aMaryChoice[$i + 1]
    $aArray[$i][1] = $aMaryChoice[0]
Next
For $j = $i To $i + UBound($aSamChoice) - 2
    $aArray[$j][0] = $aSamChoice[$j - $i + 1]
    $aArray[$j][1] = $aSamChoice[0]
Next

; And here it is
_ArrayDisplay($aArray, "Initial fill", Default, 8, Default, "Fruit|Name|1|2|3|4|5")

; Make choices for next 5 turns
For $i = 1 To 5

    ; Mary chooses randomly
    Do
        $iMarySelection = Random(1, UBound($aMaryChoice) - 1, 1)
        ; Check that choice has not already been made - choice is empty if already chosen
    Until $aMaryChoice[$iMarySelection] <> ""
    ; Get choice content
    $sMarySelection = $aMaryChoice[$iMarySelection]
    ; Clear choice to p[revent repeat
    $aMaryChoice[$iMarySelection] = ""
    ; Mark choice
    $aArray[$iMarySelection - 1][$i + 1] = "M"

    ; if Sam still has a choice
    If $i <= UBound($aSamChoice) - 1 Then
        ; Sam chooses randomly
        Do
            $iSamSelection = Random(1, UBound($aSamChoice) - 1, 1)
            ; Check selection is still available AND that is not the same as Mary
        Until $aSamChoice[$iSamSelection] <> "" And $aSamChoice[$iSamSelection] <> $sMarySelection

        ; Clear choice to prevent repeat
        $aSamChoice[$iSamSelection] = ""
        ; Mark choice
        $aArray[$iSamSelection - 1 + UBound($aMaryChoice) - 1][$i + 1] = "S"
    EndIf



    ; Here is what was chosen in this round
    _ArrayDisplay($aArray, "Choice " & $i, Default, 8, Default, "Fruit|Name|1|2|3|4|5")

Next

I hope it helps.

M23

[Peggy]

Hi Melba23,

Thanks for your help very much!!!!

If I want to add new name and Fruit,how can i to do? Thank you very much!!!!

[Melba23]

peggy,

That was fun! Here is a script which takes any number of groups and choices and tries t fit them together.

The key is the $aData array - you need a separate row for each group which holds the group name, number of choices and actual choices as shown. The groups are in priority order - the lower down the list then the more chance they will not get a valid choice (but see below). Once you have entered the groups and their choices just let it rip!

expandcollapse popup

#include <Array.au3>

; Array of groups (in priority order) and possible choices
Global $aData[][] = [["Mary", 5, "Apple", "Banana", "Tomato", "Watermelon", "Strawberry"], _
                    ["Sam", 3, "Apple", "Tomato", "Strawberry"], _
                    ["Jack", 3, "Banana", "Watermelon", "Strawberry"]]

; Determine the size of the display
Global $iRows = 0
For $i = 0 To UBound($aData) - 1
    $iRows += $aData[$i][1]
Next
Global $iCols = UBound($aData, $UBOUND_COLUMNS)

; Create array to hold display
Global $aDisplay[$iRows][$iCols]

; Create array to hold Start row data and choices left for each group
Global $aGroupInfo[UBound($aData)][2] = [[0]]
$iRow = 0
For $i = 0 To UBound($aData) - 1
    If $i Then
        $iRow += $aData[$i - 1][1]
        $aGroupInfo[$i][0] = $iRow

    EndIf

    $aGroupInfo[$i][1] = $aData[$i][1]
Next

; Fill array with choices and group
$iRow = 0
For $i = 0 To UBound($aData) - 1
    For $j = 2 To $aData[$i][1] + 1
        $aDisplay[$iRow][0] = $aData[$i][$j]
        $aDisplay[$iRow][1] = $aData[$i][0]
        $iRow += 1
    Next
Next

; Run through choices
For $iChoice = 2 To $iCols - 1
    ; Row of display to fill
    $iRow = 0
    ; Choices already made
    $sChoicesMade = ":"
    ; For each group in turn
    For $iGroup = 0 To UBound($aData) - 1
        ; Check choices still available
        If $aGroupInfo[$iGroup][1] Then
            ; Set counter to check for cases where no choices are unique
            $iCounter = 0
            While 1
                ; Choose randomly within the group choices
                $iSelection = Random(2,  $iCols - 1, 1)
                ; Increase counter and check for infinite loop
                $iCounter += 1
                If $iCounter > ($iCols - 1) * 10 Then
                    ; No available unique choice for this group
                    ExitLoop

                EndIf

                ; Check selection is still available
                $sSelection = $aData[$iGroup][$iSelection]
                If $sSelection <> "" Then
                    ; Check that it has not already been chosen
                    If StringInStr($sChoicesMade, ":" & $sSelection & ":") = 0 Then
                        ; Unique choice so add to selections made
                        $sChoicesMade &= $sSelection & ":"
                        ; And add to display
                        $aDisplay[$aGroupInfo[$iGroup][0] + $iSelection - 2][$iChoice] = $sSelection

                        ; Clear from data to prevent repeat
                        $aData[$iGroup][$iSelection] = ""
                        ; And reduce count of remaining choices
                        $aGroupInfo[$iGroup][1] -= 1
                        ; And continue with next group
                        ExitLoop

                    EndIf

                EndIf

            WEnd

        EndIf

    Next
Next

The rather clever little wrinkle (even if I say it myself) is that when there are no valid choices left for a group in a particular round, they can keep their option ready for another round when they might be able to use it.  Try running it a few times and you will see the lower groups not getting a selection in column 4 (third choice) but using the value in a later column .

M23

[Peggy]

Sorry,   I was quite busy some time ago.
Thanks for your help!!
This Help me solve a large problem, really thank you for your help!!!!!! 

[Melba23]

peggy,

Glad I could help - it was a fun problem to solve.

M23