Sign in to follow this  
Followers 0
Jfish

Secret Santa Automation

2 posts in this topic

#1 ·  Posted (edited)

***

EDIT 11/29/14: I made a huge mistake in the original as it only created pairs (i.e. if I get you - you also get me).  What I really needed was for each person to get anyone but themselves - but not necessarily create a pair.  The issue then became that if there was one person left at the end they could not get themselves.

This new version (1.1).  Does several things:

1. It creates a unique match for everyone on the list without reciprocal pairs

2. Added: deletion of blank rows before processing

3. Added checking for the word "name" in the first cell to see if headers are included

4. Sample names file included

5. GUI slightly modified

***

 

Its that time of year again.  The holidays are upon us.  Therefore, I decided to automate the creation a "secret santa" list.   These lists are popular around the holidays but usually require at least some manual labor to pair people up.  This generator takes the work out of it.  Just feed it a list of names via an Excel spreadsheet with the names populated in the first column (no blanks or headers please).  It will then generate a new file with the pairings for each name on the list.  I even paid for a graphic with usage right that is included in the zip file along with the source and Koda GUI (modified for relative file location of the image).  Enjoy.

Just the code for those that don't want the whole file:

; Secret Santa Pairing Genertaor
; Use this application to randonmly pair people for secret Santa
; All names must be entered on first col of a spreadhsheet
; no headers - no blank rows
; JFish v 1.1
;********************************

#include<Excel.au3>
#include<Array.au3>
#include <ButtonConstants.au3>
#include <GUIConstantsEx.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>
#Region ### START Koda GUI section ### Form=C:\Users\Jayme\Documents\Code\secret santa\secretSnataGUI2.kxf
$Form1 = GUICreate("Secret Santa List Generator v 1.1", 533, 331, 201, 124)
GUISetBkColor(0xFFFFFF)
$Button1 = GUICtrlCreateButton("Select Excel File With Names", 144, 200, 233, 49)
GUICtrlSetFont(-1, 8, 800, 0, "MS Sans Serif")
GUICtrlSetBkColor(-1, 0xA6CAF0)
$Pic1 = GUICtrlCreatePic(@ScriptDir&"\400pxSecretSanata.bmp", 64, 8, 404, 172)
$Label1 = GUICtrlCreateLabel("Press the above button to select an Excel file containing names in Col A", 16, 264, 507, 24)
GUICtrlSetFont(-1, 12, 400, 0, "MS Sans Serif")
$Label2 = GUICtrlCreateLabel("The software will randomly produce Secret Santa pairs  and display then in a new file", 16, 293, 502, 20)
GUICtrlSetFont(-1, 10, 400, 0, "MS Sans Serif")
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###


func _createList()
; will be used to control the while loop - counts the number of pairs created
dim $pairs=0
; find a an Excel sheet containing names in Col 1
$path=FileOpenDialog("Select File With Secret Santa Names",@ScriptDir,"Excel (*.xl*)" )
    If @error Then
            ; Display the error message.
            MsgBox($MB_SYSTEMMODAL, "", "No file(s) were selected.")
    else
        ; open excel file from FileOpenDialog
        GLOBAL $excel=_Excel_Open()
        Local $oWorkbook = _Excel_BookOpen($excel, $path)
        ;delete all blank rows in Col A
        $oWorkbook.ActiveSheet.Columns("A:A").SpecialCells($xlCellTypeBlanks).EntireRow.Delete
        ; find last non blank row
        $LastRow = $oWorkbook.ActiveSheet.Range("A1").SpecialCells($xlCellTypeLastCell).Row
        ;find text in cell A1 to make sure it is not a header
        Local $aResult = _Excel_RangeRead($oWorkbook, Default, "A1")
        ;non-comprehensive data check to make sure the number of names is even for pairing and A1 does not contain header
        if mod($LastRow,2)<>0 or StringInStr($aResult,"name")<>0 then
            MsgBox("","Data Error","You need to have an even number of names and the file should not contain headers")
        else
            $namesArray = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A"))
            ; close that workbook
            _Excel_BookClose($oWorkbook)

            ; create new 2D array to store the pairings
            dim $pairingArray[ubound($namesArray)][3]

            ; populate first row of new array
            For $a=0 to UBound($pairingArray)-1
                $pairingArray[$a][0]=$namesArray[$a]
            Next

            ; this starts a loop that will run until everyone has a pairing
            while $pairs<(UBound($pairingArray)-1)

                ;this loop looks for a partner for each person on the list sequesntially
                for $names=0 to UBound($pairingArray)-1
                    ;does the name have anyone assigned to it? If not then ...
                    if $pairingArray[$names][1]=="" Then
                        ;generate random number
                        local $randomNumber=round(Random(0,ubound($pairingArray)-1),0)
                            ; creates to VARs for name on list and randomly generated name used for comparison
                            $originalName=$pairingArray[$pairs][0]
                            $randomName=$pairingArray[$randomNumber][0]
                            ;checks to ensure that if only one name is left on the list without a partner that you need
                            ;to erase everything and do it again ... this one drove me CRAZY
                            if $pairs==UBound($pairingArray)-2 AND $pairingArray[UBound($pairingArray)-1][1]=="" AND $pairingArray[UBound($pairingArray)-1][2]=="" Then
                                $pairs=0
                                for $deleteNames=0 to ubound($pairingArray)-1
                                    $pairingArray[$deleteNames][1]=""
                                    $pairingArray[$deleteNames][2]=""
                                next
                                ;ConsoleWrite(@crlf&"!!!DEBUG: NEW LOOP TRIGGEED !!!")
                                ExitLoop
                            EndIf
                            ; if the name random name is the same as the person you are matching OR the person you are
                            ; attempting to match has already been assigned then keep trying ...
                            if ($originalName==$randomName) OR ($pairingArray[$randomNumber][2]=="matched") then
                                do
                                $randomNumber=round(Random(0,ubound($pairingArray)-1),0)
                                until ($pairingArray[$pairs][0]<>$pairingArray[$randomNumber][0]) AND ($pairingArray[$randomNumber][2]<>"matched")
                            Endif
                            ; random person looks good so write it to the array
                            $pairingArray[$names][1]=$pairingArray[$randomNumber][0]
                            ; mark that person as matched so we don't pick them again
                            $pairingArray[$randomNumber][2]="matched"
                            ; increment the condition for the outer while loop
                            ; in this case keep going until we have the whole list
                            $pairs+=1
                    EndIf
                next
            WEnd
            ; paste it to a new results Excel spreadsheet
            GLOBAL $resultsWorkbook=_Excel_BookNew($excel)
            _Excel_RangeWrite($resultsWorkbook,$resultsWorkbook.Activesheet,$pairingArray,"A1")
        EndIf; this one is for the even number check
    EndIf
EndFunc


While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        case $Button1
            _createList()
        Case $GUI_EVENT_CLOSE
            Exit

    EndSwitch
WEnd

secretsanta1.1.zip

Edited by Jfish

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

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
Sign in to follow this  
Followers 0

  • Similar Content

    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have to store an entire row of a Excel workbook into an array.  The row index is stored in a variable.
      How can i do it?
      Thanks in advance for your support.
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have to write a value into an excel column.
      I know where it starts from, but i don't know what the end is, last non-empty cell.
      How can i get the number of last non-empty cell?
      Thanks in advance.
      Regards 
    • Nareshm
      By Nareshm
      Hi All,
      I have excel file like this
      and i want to cut cell/text from excel to other software.

       
      I have to cut the cell of B column one by one and past into other software
      If Winexists("No Data Found")
      then restore cuted cell and goto next/down side cell
      How to do it ?
    • JustinZandee
      By JustinZandee
      I need a random string generator which creates 15 letters/numbers.
      How can I make that?
    • water
      By water
      Extensive library to control and manipulate Microsoft Excel charts.
      Theads: General Help & Support - Example Scripts
      BTW: If you like this UDF please click the "I like this" button. This tells me where to next put my development effort

      KNOWN BUGS (last changed: 2017-07-21)
      None. The COM error handling related bugs have been fixed.