Peggy

excel table with arrary and for next loop for scheduling problems

7 posts in this topic

#1 ·  Posted (edited)

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

1.thumb.jpg.adb3f3d9bda8b04cd669b142223c
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.thumb.jpg.ef68af61c60201eacf8b8806ffff

 

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

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

4.thumb.jpg.142b4272bba2cfd65ba957c9afb5

 

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

5.thumb.jpg.b2d4eeff68ed8f41ba5cf0d3fa9a

 

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

6.thumb.jpg.a2d10e8e3ba4206c2cd29d71849b

 


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 by markwu

Share this post


Link to post
Share on other sites



start with this:

_Excel_Open()

 


Wondering who uses AutoIT and what it can be used for ?


ADO.au3 UDF     POP3.au3 UDF     XML.au3 UDF    How to use IE.au3  UDF with  AutoIt v3.3.14.x  for other useful stuff click the following button:

Spoiler

Last update: 2017-05-03
Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind. 

My contribution (my own projects): * Debenu Quick PDF Library - UDF * Debenu PDF Viewer SDK - UDF * Acrobat Reader - ActiveX Viewer * UDF for PDFCreator v1.x.x * XZip - UDF * AppCompatFlags UDF * CrowdinAPI UDF * _WinMergeCompare2Files() * _JavaExceptionAdd() * _IsBeta() * Writing DPI Awareness App - workaround * _AutoIt_RequiredVersion() * Chilkatsoft.au3 UDF * TeamViewer.au3 UDF * JavaManagement UDF * VIES over SOAPPOP3.au3 UDF *  RTF Printer - UDF * XML.au3 - BETA * ADO.au3 UDF SMTP Mailer UDF * WinSCP UDF *

My contribution to others projects: * _sql.au3 UDF  *

Useful links: * Forum Rules * Forum etiquette *  Forum Information and FAQs * How to post code on the forum * AutoIt Online Documentation * AutoIt Online Beta Documentation * SciTE4AutoIt3 getting started * Convert text blocks to AutoIt code * Games made in Autoit * Programming related sites * Polish AutoIt Tutorial * DllCall Code Generator * 

Wiki: Expand your knowledge - AutoIt Wiki * Collection of User Defined Functions * How to use HelpFile * Best coding practices * 

IE Related:  * How to use IE.au3  UDF with  AutoIt v3.3.14.x * Why isn't Autoit able to click a Javascript Dialog? * Clicking javascript button with no ID * IE document >> save as MHT file * IETab Switcher (by LarsJ ) * HTML Entities * _IEquerySelectorAll() (by uncommon) * 

I encourage you to read: * Global Vars * Best Coding Practices * Please explain code used in Help file for several File functions * OOP-like approach in AutoIt * UDF-Spec Questions *  EXAMPLE: How To Catch ConsoleWrite() output to a file or to CMD *

"Homo sum; humani nil a me alienum puto" - Publius Terentius Afer
"Program are meant to be read by humans and only incidentally for computers and execute" - Donald Knuth, "The Art of Computer Programming"
:naughty:  :ranting:, be  :) and       \\//_.

Anticipating Errors :  "Any program that accepts data from a user must include code to validate that data before sending it to the data store. You cannot rely on the data store, ...., or even your programming language to notify you of problems. You must check every byte entered by your users, making sure that data is the correct type for its field and that required fields are not empty."

 

Share this post


Link to post
Share on other sites

peggy,

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

#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

1 person likes this

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

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!!!!

7.thumb.jpg.dd012e85cc99afe7520029f59a07

Share this post


Link to post
Share on other sites

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!

#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


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

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!!!!!! :D

Share this post


Link to post
Share on other sites

peggy,

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

M23

1 person likes this

Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


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