Peggy

excel table with arrary and for next loop for scheduling problems_2

6 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, Jack

1.thumb.jpg.fbde7c98e1c9ffd7f6557dcd7ec1
2. Every person eating a fruit, and each times one person can eat the same fruit (or random fruit)
3. Last eaten fruit can not eat
4. so be scheduled
5. If the previous round fruits are finished, the remaining number can pick one randomly 
6. If I have more than 100 the number of Name(people), how can I to code it?


Picture:
1. The first time Mary eat Apple, Sam  can  eat Apple(or random),and Jack chose random first to eat.

2.thumb.jpg.6688381fa3a5b3b41c32c2339378
2. The second They are chose random first to eat.

3.thumb.jpg.f4d7feefb9c8e81dd9cdbce4f3e1
3. Third  They are chose random first to eat.

4.thumb.jpg.74c2575768754dab0472b90ea449
4. Fourth Sam chose tomato,because the previous round fruits are finished, the remaining number can pick one randomly ,Jack is too.

5.thumb.jpg.7836a56d5a33b26ed88d7ee564e1
5. Fifth Sam chose tomato,because the previous round fruits are finished, the remaining number can pick one randomly ,Jack is too.

 

6.thumb.jpg.64950d4f51ef58151cdac005d58d


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

 

 

 

 

 

 

Edited by Peggy

Share this post


Link to post
Share on other sites



Peggy,

A couple of your requirements are unclear:

- Every person eating a fruit, and each times one person can eat the same fruit (or random fruit): Does this mean that in each round several pairs can eat the same fruit or that only one pair may do so?

- If the previous round fruits are finished, the remaining number can pick one randomly: So those with a smaller range from which to choose just repeat choices from that range until the end of the largest range? What if there are no unique (or double) choices left in the round after all the previous selections? What do they choose then?

As to doing this for 100+ people, I think that you might begin to struggle. What exactly are you doing? If we get a better idea of the purpose of all this we might be able to offer a better solution.

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

#3 ·  Posted (edited)

Hi Melba23,
Thanks for your reply!!!I feel very moved!! :D


- Every person eating a fruit, and each times one person can eat the same fruit (or random fruit):
each round one person can eat the same fruit,but next time one person can't eat previous eaten fruit(Unless the previous have all finished).

- If the previous round fruits are finished, the remaining number can pick one randomly:
This function can be deleted, if the previous finish, behind may be left blank.(If that can to do, can be randomly selected)

As shown below

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

47.thumb.jpg.4ff74b560b00ddc5c1e64a4f7ff

1. Round 1: each one Random choice.(Each one can choose the same fruit)

 

48.thumb.jpg.6c6f4a914cdc841a749be657ef6

2. Round 2: each one Random choice.(each one can't eat Previous fruit Unless all eaten up)

49.thumb.jpg.255a6b2020594034b5344c98b93

3. Round 3,4,5,6: And so on,  each one Random choice.(each one can't eat Previous fruit Unless all eaten up,This feature is not essential

50.thumb.jpg.1ede09792d8c69ed8043b97ba46

Thanks for your  great help!! :D

 

p.s.
If name more than the amount of fruits (Ex: name x7, fruit max: 5), than someone will be blank at previous round, we hope to end with five round.

44.thumb.jpg.03215a14ba4bff1a15ea0a57735

we hope result is  .. 

 

43.thumb.jpg.a3ae7c8c35dff59f1732562709d

Thanks for your  great help!! :D:D

Edited by Peggy

Share this post


Link to post
Share on other sites

Peggy,

I am afraid I am still not altogether clear about your requirements, but this seems to match your final state:

3. Round 3,4,5,6: And so on,  each one Random choice.(each one can't eat Previous fruit Unless all eaten up

 

#include <Array.au3>

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

; 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 group data
Global $aGroupInfo[UBound($aData)][4] = [[0]]
Local $iRow = 0
For $i = 0 To UBound($aData) - 1
    If $i Then
        $iRow += $aData[$i - 1][1]
        $aGroupInfo[$i][0] = $iRow          ; Start row
    EndIf

    $aGroupInfo[$i][1] = $aData[$i][1]      ; Total number of choices
    $aGroupInfo[$i][2] = $aData[$i][1]      ; Choice index
    Local $aChoices[$aData[$i][1]]
    For $j = 2 To $aData[$i][1] + 1
        $aChoices[$j - 2] = $aData[$i][$j]
    Next
    _ArrayShuffle($aChoices)
    $aGroupInfo[$i][3] = $aChoices          ; Shuffled array of choices
Next

; Fill display with groups names and choices
$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 choice rounds
For $iChoice = 2 To $iCols - 1
    ; For each group in turn
    For $iGroup = 0 To UBound($aData) - 1
        ; Get next choice from the shuffled array
        $sChoice = ($aGroupInfo[$iGroup][3])[$aGroupInfo[$iGroup][2] - 1]
        ; Check choices remain
        If $aGroupInfo[$iGroup][2] > 1 Then
            ; Move to next choice for next round
            $aGroupInfo[$iGroup][2] -= 1
        Else
            ; Reset choice count and reshuffle array
            $aGroupInfo[$iGroup][2] = $aGroupInfo[$iGroup][1]
            _ArrayShuffle($aGroupInfo[$iGroup][3])
        EndIf

        ; Find choice in the original array
        $iIndex = _ArraySearch($aData, $sChoice, Default, Default, Default, Default, Default, $iGroup, True)
        ; Add to display in correct row
        $aDisplay[$aGroupInfo[$iGroup][0] + $iIndex - 2][$iChoice] = $sChoice

    Next
Next

_ArrayDisplay($aDisplay, "", Default, 8)

Does it do what you want? If not, what needs to be changed?

M23

P.S. And I am still curious as to what exactly you are doing as this algorithm is obviously not meant for fruit selection in the real world and there might be a better way to do it if we knew the overall purpose.


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,

That is the  fuction I want, thank you for helping me so much ,I'm so happy!!
About this program, this is the process of class scheduling
.I just say for an example:eating fruit, that will be easy to understand for you.
Thank you very very much !!!!!You're the best person I've ever seen~!!:lol:^_^

Share this post


Link to post
Share on other sites

Peggy,

Delighted I could help. Do not hesitate to let me know if your supervisor changes the requirements again!

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

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

  • Similar Content

    • CarlD
      By CarlD
      I'm a command-line kind of guy, and I write scripts primarily for myself.  Since many websites nowadays require strong passwords, I thought I'd write a simple password generator in AutoIt. I know that AutoIt mavens have written more elaborate pw generators; I offer mine for what it's worth. The compiled script, GenPass.exe, can be downloaded here. See below for Help text and source. Enjoy!
      Updates:
      2017-05-06: Default password changed to variable length of 13-22 characters; argument "1" no longer supported
                            When compiled as GenPW.exe, password is sent directly to the clipboard, no message box unless password generation fails.
      2017-05-05: Correction to bypass password generation if argument is ?|H|h
      2017-05-03: Added special argument 1 to generate a password of variable length (10-18 characters) including two (2) separator characters
      2017-05-02: Added option /S to set a (persistent) randomization seed
      Help:
      GenPass.exe|GenPW.exe -- CLD rev. 2017-05-06
      Generate a strong password and save it to the Windows clipboard
      Note: GenPW.exe has the same functionality as GenPass.exe, but
            sends the generated password directly to the clipboard.
            No message box is displayed (unless password generation fails).
        
      "Strong" means that the password contains random combinations of
      alphnumeric characters, including at least one uppercase letter
      (A-N,P-Z), one lowercase letter (a-k,m-z), and one number (0-9).
      (Generated passwords do not use uppercase O or lowercase l as
      these characters are easily confused with the numbers 0 and 1.)
      The length of the password is up to you (see Usage, below),
      but needless to say, the longer, the stronger.
      By default, GenPass generates a strong password of between 13
      and 22 characters that includes two of the following separator
      characters: $%&()*+,-./:;@[]_. Alternatively, you can supply a
      command-line argument in which any number n from 1 to 9 stands
      for a random sequence of alphanumeric characters of length
      n, and any other character stands for itself. Thus, you can
      include fixed words and other characters, such as separators,
      in the generated password. Spaces in the argument are converted
      to underscores. Here are some examples:
      Usage             Sample output
      -----             -------------
      GenPass           MqU26A*6dS-53r8
      GenPass 9         frdhPYDs9
      GenPass 58        weoXYHKxDI1uQ
      GenPass 5.5       UfA6j.43VBB
      GenPass 3-4-3     0I0-6gq4-njc
      GenPass 5,3.7     I2FSR,tRZ.fjeIsFy
      GenPass 3)5(3     UMf)m8513(CBq
      GenPass 3[haha]3  yLa[haha]P3y
      GenPass Yes way5  Yes_way1BsUh
      Seed Option (/S)
      ----------------
      Adding switch /S to the command-line argument causes GenPass to
      set a seed for the random generation of password characters. A
      bare /S sets a randomized seed which is written to disk in a file
      named GenPass.rnd; this seed is used for all subsequent launches
      of GenPass with the bare /S option. Alternatively, you can specify
      a seed (range -2^31 to 2^31-1) on the command line with /S [seed].
      Here are some examples:
      GenPass /S
      GenPass /S 33.3333
      GenPass 5,5,5 /S
      GenPass 5,5,5 /S 33.3333
      Note that any subsequent launch of GenPass without the /S option
      will cause GenPass.rnd to be deleted.
      Source:
      #Region ;**** Directives created by AutoIt3Wrapper_GUI **** #AutoIt3Wrapper_Outfile=GenPass.exe #AutoIt3Wrapper_UseUpx=y #EndRegion ;**** Directives created by AutoIt3Wrapper_GUI **** #cs GENPASS.AU3 -- AutoIt v3 CLD rev.2017-05-05 ------------------ Generate a strong password and save it to the clipboard >> Command GenPass ? for detailed help << ------------------------------------------------------- #ce #include <Clipboard.au3> #include <FileConstants.au3> #include <MsgBoxConstants.au3> #include <StringConstants.au3> AutoItSetOption("WinTitleMatchMode", -4) FileInstall ("d:\path\GenPass.htm", @ScriptDir & "\GenPass.htm", $FC_OVERWRITE) ; Template/Seed Local $sTemp = "" Local $bSeed = False, $fSeed=False If $CmdLine[0] Then $sTemp = $CmdLineRaw If $CmdLine[$CmdLine[0]] = "/s" Then $bSeed = True $sTemp = StringTrimRight($sTemp, 2) $sTemp = StringStripWS($sTemp, $STR_STRIPTRAILING) EndIf If $CmdLine[$CmdLine[0] - 1] = "/s" Then $bSeed = True $fSeed = $CmdLine[$CmdLine[0]] $sTemp = StringTrimRight($sTemp, 3 + StringLen($fSeed)) $sTemp = StringStripWS($sTemp, $STR_STRIPTRAILING) EndIf EndIf If Not $sTemp Then $sTemp = "8" If $sTemp = "1" Then $aSeps = StringSplit("#$%&()*+,-./:;@[]_", "") $sTemp = String(Random(3,6,1)) & $aSeps[Random(1,$aSeps[0],1)] & _ String(Random(2,4,1)) & $aSeps[Random(1,$aSeps[0],1)] & _ String(Random(3,6,1)) EndIf $sFn = @ScriptDir&"\GenPass.rnd" If $bSeed Then If Not $fSeed Then If Not FileExists($sFn) Then $fSeed = Random(-1.999^31,1.999^31,0) $h=FileOpen($sFn,2) If $h > -1 Then FileWrite($h,$fSeed) FileClose($h) Else Exit MsgBox($MB_ICONWARNING, @ScriptName, "Error opening " & $sFn) EndIf Else $h=FileOpen($sFn) If $h > -1 Then $fSeed=FileRead($h) FileClose($h) Else Exit MsgBox($MB_ICONWARNING, @ScriptName, "Error opening " & $sFn) EndIf EndIf EndIf SRandom($fSeed) Else If FileExists($sFn) Then FileDelete($sFn) EndIf ; Show help If StringInStr("?Hh", $sTemp) Then If WinExists("[REGEXPTITLE:GenPass.exe:.*]") Then WinActivate("[REGEXPTITLE:GenPass.exe:.*]") Else ShellExecute(@ScriptDir & "\GenPass.htm") EndIf Exit EndIf ; Main $sTemp = StringReplace($sTemp, " ", "_") $iC = 1 While $iC < 10001 $sPW = GenPW($sTemp) If $sPW Then ClipPut($sPW) If Not StringInStr (@ScriptName, "GenPW") Then _ MsgBox($MB_ICONINFORMATION, @ScriptName, $sPW & _ " saved to clipboard" & @CRLF & @CRLF & _ @ScriptName & " ? shows detailed help") Exit Else $iC += 1 EndIf WEnd Exit MsgBox($MB_ICONWARNING, @ScriptName, "Password generation failed!") ;------------------------------- Func GenPw($sTemplate) Local $aIn = StringSplit($sTemplate,"") Local $sOut = "" Local $sABC = _ "0123456789ABCDEFGHIJKLMNPQRSTUVWXYZabcdefghijkmnopqrstuvwxyz0123456789" Local $aAB = StringSplit($sABC, "") Local $bUC = 0, $bLC = 0, $bNR = 0 For $i = 1 To $aIn[0] If Int($aIn[$i]) Then $iK = $aIn[$i] For $j = 1 To $iK $iR = Random(1, $aAB[0],1) Select Case StringInStr("0123456789", $aAB[$iR]) $bNR = 1 Case StringInStr("ABCDEFGHIJKLMNPQRSTUVWXYZ", _ $aAB[$iR], $STR_CASESENSE) $bUC = 1 Case StringInStr("abcdefghijklmnpqrstuvwxyz", _ $aAB[$iR], $STR_CASESENSE) $bLC = 1 EndSelect $sOut &= $aAB[$iR] Next Else $sOut &= $aIn[$i] EndIf Next If ($bUC And $bLC And $bNR) Then Return $sOut Else Return 0 EndIf EndFunc  
    • IamKJ
      By IamKJ
      So I have a lot of different functions in the little program I'm making.  Most of them use the Do statement, such as finding images, clicking buttons, etc.
       
      The question I have is how would I be able to both
      1) Create a hotkey to pause whatever I am doing at the moment.  If I have to put something into every function in the program, I don't mind.
      2) Create a hotkey to exit whatever I am doing and stop all loops or commands and bring back the main GUI?
    • nlta1992
      By nlta1992
      I want creat GUI, have button 1 to 10, but only use loop for ... to ... step ... next.
      Thanks.
    • SkysLastChance
      By SkysLastChance
      I keep losing the count of my $r varable when I go into the dropdown () function and call the same function.
      #include <Excel.au3> #include <AutoItConstants.au3> #include<GUIConstantsEx.au3> #include<EditConstants.au3> #include<GUIConstants.au3> Global $iBox, $oExcel, $oWorkbook, $r = 1, $x = 3 HowMany() Excel () Dropdown () Sleep (500) Func Dropdown () $list = GUICreate("Chart", 225, 80) $combobox = GUICtrlCreateCombo("Clinic 1", 10, 10, 120, 20) GUICtrlSetData(-1, "Clinic 2|Clinic 3|Clinic 4") $button = GUICtrlCreateButton("Select", 10, 40, 60, 20) GUISetState(@SW_SHOW) While 1 $guibox = GUIGetMsg() Select Case $guibox = $button Select Case GUICtrlRead($combobox) = "Clinic 1" GUIDelete($list) Auto () EndSelect Case $guibox = $GUI_EVENT_CLOSE EndSelect WEnd EndFunc Func HowMany() Local $iMsg While 1 ;~ Turn input into a number $iBox = Number(InputBox ("Regestration", "How Many Patients are there total?")) ;~ If user enters a string or cancels then get them to try again If $iBox = 0 Then $iMsg = MsgBox(1,'Regestration', 'Please enter a valid number') If $iMsg = 2 Then Exit Else ExitLoop EndIf WEnd EndFunc Func Excel() While ProcessExists("EXCEL.EXE") $ms = MsgBox(5,"","Process error. You have an Excel sheet open. You must close it in order to let this program work. Please close it now.") If $ms=2 Then Exit ;~ Doesn't require sleep since the script is paused by the MsgBox above ;~ Sleep(250) WEnd ;~ Shouldn't use Global Scope inside functions moved to top of script Local $sExcelFile = FileOpenDialog("Choose/Create Excel File", @ScriptDir, "(*.xlsx)") If FileExists($sExcelFile) Then ;~ Shouldn't use Global Scope inside functions moved to top of script $oExcel = _Excel_Open () $oWorkbook = _Excel_BookOpen($oExcel,$sExcelFile) ;this will open the chosen xls file. Else $oExcel = _Excel_Open() $oWorkbook = _Excel_BookNew($oExcel, 2);this is here to create the xls file if it does not exist. EndIf EndFunc Func Auto() Local $aArray1 = _Excel_RangeRead($oWorkbook) ;~ If $iBox is greater than no. of rows in $aArray then $iBox equals the number of rows in $aArray If $iBox > (UBound($aArray1) - 1) Then $iBox = UBound($aArray1) - 1 For $i = 2 To UBound($aArray1) - 1 ;$i =0 Start from row A If $aArray1[$i][1] = "" Then Continueloop $sR0 = $aArray1[$i][0] ;status $sR1 = $aArray1[$i][1] ;Last Name $sR2 = $aArray1[$i][2] ;First Name $sR3 = $aArray1[$i][3] ;DOB $sR4 = $aArray1[$i][4] ;Sex $sR5 = $aArray1[$i][5] ;Mailling Address $sR6 = $aArray1[$i][6] ;Zip $sR7 = $aArray1[$i][7] ;Phone # $sR8 = $aArray1[$i][8] ;Visit Reason $sR9 = $aArray1[$i][9] ;Insurance $sR10 = $aArray1[$i][10] ;Clinic $sR11 = $aArray1[$i][11] ;Provider $sR12 = $aArray1[$i][12] ;Appt Time $sR13 = $aArray1[$i][13] ;Appt Date WinWaitActive ("Untitled - Notepad") ControlSend("Untitled - Notepad", "", "", $sR1 & ',' & $sR2 & @CR) $r += 1 If $r > $iBox Then Exit Dropdown () Next EndFunc so the second time I choose clinic 1 I want it to go to the next row which would be Champ brett in my example excel.
       
      Test.xlsx
      Example Format.xlsx
    • kevin42036
      By kevin42036
      Hi All,

      I am new to AutoIt and programming in general so any help would be greatly appreciated! 

      I am trying to create a script mainly using MouseClick and WinWait in order to automate some web processes. However, due to the lag time of the web pages and fields, sometimes my script does not run properly and I need to rerun it. I am trying to create a script that will check to see if the final line of the script was properly executed and if not, rerun the program and increase a count. Ultimately I would like some sort of report that tells me how many times it took to run to completion and total time. 

      Does anyone have any suggestions as to which functions I can use to accomplish this? I am also open to any suggestions on a better process to ensure that my program runs to completion.

      Thanks in advance!

      Kevin