Jump to content
Sign in to follow this  
Jfish

Secret Santa Automation

Recommended Posts

Jfish

***

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  

  • Similar Content

    • Skeletor
      By Skeletor
      Hi Guys,
      Can anyone point me in the right direction. I'm trying to accomplished conditional formatting with arrows, but the code I have is wrong... 
      .Range("=$A3:$A4000").FormatConditions.Add = (xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets I also tried it like this:
      .Range("=$A3:$A4000").FormatConditions _ .Add(xlCellValue, xlGreater, "=$A:$A") .IconSet(xl3Arrows) ;ActiveWorkbook.iconsets  
    • Skeletor
      By Skeletor
      Hi Guys,
      How would you use _Excel_FilterSet to filter the excel sheet from largest number to smallest number?
      _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">20", 1, "<40") ; OR _Excel_FilterSet($oWorkbook, "Sheet1", "A2:I2", 1, ">", 1, "<") Excel Sheet attached. Trying to filter on the last column "I2"
      Inventory.xlsx
    • Skeletor
      By Skeletor
      Hi All,
      Has anyone come across this before?
      Code below:
      $SheetList = _Excel_SheetList($oWorkbook) _FileWriteFromArray("C:\" & $SheetListOutput, $SheetList, 1) Result:
      ResultABC| ResultDEF| ResultGHI| ResultJKL| ResultMNO| It created these "|" vertical bars?
    • Skeletor
      By Skeletor
      Hi All,

      I would like to know how you would take a FileLineRead and insert it into an array which then inserts it into Excel?
      One thing to know is the files content is broken up, so I only use half of the content within $FileRead1.
      So its imperative that the $value1, $value2, etc variables be used. 
      Code below:
      $FileRead1 = FileReadLine("C:\temp\sample.txt",1) For $count = 1 To _FileCountLines($FileRead1) Step 1 $string = FileReadLine($FileRead1, $count) $input = StringSplit($string, ",", 1) $value1 = $input[1] $value2 = $input[2] $value3 = $input[3] $value4 = $input[4] _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value1, "A1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value2, "B1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value3, "C1") _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $value4, "D1") Next  
    • AnonymousX
      By AnonymousX
      Hello,
      I'm trying to write a script that moves copies excel cells into an array. I'll than manipulate the values and send array into another program. 
      I don't want range to be specific to a workbook, or sheet, or set of cells.
      I want user to be able to highlight desired cells and to copy either normally ("Ctrl+C") or by a hotkey ("Alt+C"). 
      Could someone help me with this?
      Thank you,
      I've tried to write the framework: (edited)
      #include <MsgBoxConstants.au3> #include <Array.au3> #include <Excel.au3> HotKeySet("!v", "Pastedata") While True Sleep(1000) WEnd func Makearray() local $bArray ;User has cells already copied ;Convert clipboard into an array ;I don;t know how excel stores data to clipboard so don;t know how to bring it into array _Arraydisplay($bArray) MsgBox(0,0,$bArray) return $bArray endfunc func Pastedata() Local $aArray MsgBox(0,0,"wait",1) ;make array based on assumption user has already copied a range to clipboard $aArray = Makearray() ;paste code ;don;t worry about this I got the rest endfunc  
×