Results 1 to 5 of 5

Thread: anual leave roster issue

  1. #1

    anual leave roster issue



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All,
    I basically need help with the following staffing issue in connection with our Annual leave “ROSTERS”. we don't have a system at work presently to deal with annual leave swaps.

    At work all Staff are assigned annual Leave 6wks per year, these 6 week blocks are represented by alpha letters such as A,B, D, E, F, etc. As I said these letters correspond to 6 week blocks within each calendar year, this allocated leave letter remains with you throughout your employment. So for obvious reasons this doesn’t suit every member of staff, so our HR Dep’t kindly allows us to swap with other willing staff members, so basically if we could find another staff member willing to swap letters then they will allow us to do the swap, on a one time only basis per year, meaning once swaped then you cannot later on then change your mind and request to swap the swap. How staff achieve this presently is to send out an e-mail to all staff requesting what you Have and what period you Want (it's a fairly large company with over 300 staff).

    This solution works reasonably well, but is obviously flawed (I'll explain why later on) as some staff will lose out. As you will only get a response(via Email) if someone has what you want e.g meaning Staff member1 has "A" and wants "B" period and staff member20 has "B" and really wants "A", because then its just a matter of both parties getting together and filling in a request form too give to the HR Dept(2Xway swap).

    What I would like to do is create a register so as our HR Dept can accomodate all our staff a little more better, then some staff will not lose out. Following is a very basic example of why staff do lose out on swaps that actually are available but get missed due to swaps only being available via e-mail(2Xway swap).

    E.g. take the following scenario:

    If a staff member1 sends an email saying that he has “A” and wants “C”, and another staff member2 reads this request who does have “C” but he wants “B”, then he's not going to answer the e-mail and so both staff members will lose out! as the person with “C” is not interested in taking “A” as he wants “B” which is a whole different six week block.
    in the year (could be for a wedding or something so specific dates are important to get). But if we introduce a third staff member25 who say has “B” and wants “A”, then each of these 3Xstaff members would then be able to swap with each other as all staff involved would get what letter they wanted. (By the way I forgot to mention if staff member25 read any of the two previous e-mails he would not have answered either of them, I hope that makes sense
    A - C
    C - B
    B - A

    So the solution would be to create a register and then somehow be able to run some sort of formula to find the matches/swaps even it were a 4,5 or 6Xway swap as they all would be happy.
    Here’s what I have so far in VBA but it does not take into account if all parties involved are satisfied. As presntly it just finds matches and swaps whether or not the swap has been satisfied at it's end, so I think it needs nested loops holding variables for each swap so it can undo if after swapping out if all parties involved in the swap are happy , but this is something way beyond my vba skills. Any help with this is appreciated. thanks

    Code:
    Sub SWAPpart1()
    Dim lRow As Long, x1 As Long, varTmp As Variant, tmpArr() As String
    
    lRow = Range("A" & Rows.Count).End(xlUp).Row
    
    
    With Range("C2:C" & lRow)
        .Formula = Replace("=IFERROR(""A""&AGGREGATE(15,6,ROW($B$2:$B$@)/($A$2:$A$@=B2),COUNTIF(B$2:B2,B2)) & ""-A""&ROW(),"""")", "@", lRow)
        .Value = .Value
    End With
    End Sub
    Code:
    Sub SWAPRun()
    Dim lRow As Long, x1 As Long, varTmp As Variant, tmpArr() As String
    
    lRow = Range("A" & Rows.Count).End(xlUp).Row
    
    
    With Range("C2:C" & lRow)
        .Formula = Replace("=IFERROR(""A""&AGGREGATE(15,6,ROW($B$2:$B$@)/($A$2:$A$@=B2),COUNTIF(B$2:B2,B2)) & ""-A""&ROW(),"""")", "@", lRow)
        .Value = .Value
    End With
    
    With Range("D2:D" & lRow)
       .Formula = Replace("=IFERROR(""A""&AGGREGATE(15,6,ROW($B$2:$B$@)/($A$2:$A$@=B2),COUNTIF(B$2:B2,B2)) & ""-A""&ROW(),"""")", "@", lRow)
        .Value = .Value
    End With
    
    
    For x1 = 2 To lRow
        If Range("C" & x1).Value = vbNullString Then
            ' NOTHING
        Else
            tmpArr = Split(Range("C" & x1).Value, "-")
            varTmp = Range(tmpArr(0)).Value
            Range(tmpArr(0)).Value = Range(tmpArr(1)).Value
            Range(tmpArr(1)).Value = varTmp
        End If
        Call SWAPpart1
    Next x1
    End Sub
    A B C
    1 Value_Col1 Value_Col2 MATCHES
    2 B D A5-A2
    3 A C A4-A3
    4 D A A2-A4
    5 C B A3-A5
    6 G F A7-A6
    7 B B A14-A7
    8 E E A6-A8
    9 H D
    10 F G A8-A10
    11 G J
    12 E A
    13 L A
    14 K K A12-A14



  2. #2
    Anybody out there??
    Last edited by Bob Phillips; 2015-02-24 at 11:31 AM. Reason: Unnecessary quote

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,306
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Quote Originally Posted by gint32
    somehow be able to run some sort of formula to find the matches/swaps even it were a 4,5 or 6Xway swap as
    Hey there,

    I think the issue we're having here is that you're asking for something VERY complicated. I'm not even sure where to begin on this, and reality is that I'm too busy to try and figure it out. I apologize, but I just don't think I can add much value for you...
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  4. #4
    I too looked at your original post and admit I had little idea of what you wanted to do, and even less as to how your data was structured. A workbook, with some data and an example of what you want to get to might help, although it is still very complex.

  5. #5
    Quote Originally Posted by Bob Phillips View Post
    I too looked at your original post and admit I had little idea of what you wanted to do, and even less as to how your data was structured. A workbook, with some data and an example of what you want to get to might help, although it is still very complex.
    I have attached a workbook with some sample data. I'll try and explain again, I have an annual leave roster issue, So presently at work all Staff are assigned annual Leave letters representing 6wks per year, these 6 week blocks are as I said represented by letters such as A,B,C D, E, F,G,H. As I said these letters correspond to a certain 6 week block within each calendar year, this allocated leave letter remains with that person throughout there employment. So for obvious reasons this doesn’t suit every member of staff, as they have to take the allocated leave at certain moths of the years depending on the leave letter allocated. The HR Dep’t allows us to swap out of this allocated blocked with other staff members, basically we have to find another staff member willing to with their letter. How staff achieve this presently is to send out an general e-mail to all staff requesting what you Have and what period you Want. Such as I have A and I require E(which is represents certain leave blocks throughout the year)

    So put simply, I need a program to find not just 2Xway swaps like the following : Staff member has A and wants D, Staff 5 Has D and wants A so they can do a 2Xway swap...and we don't need to a program to figure this out, but what I do need a program for is something that'll search through two columns of Data and find matches/swaps that otherwise would be missed whether that be 3X, 4X or 5X etc etc way swaps : take the following (4Xway swap )example staff 1= Has A<>Wants G, staff 2=B<>D, staff 3=E<>A, staff4=G<>B. then all these people can swap as they all can get what they are chasing. see attached
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •