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