trouble with matching values in two columns with a twist

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 swapped 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 Dep’t can accommodate 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 within the that year (it 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 presently 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(see attached xls)

PHP 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 


PHP 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 


previous notes: - Here’s what I have so far in VBA but it does not take into account if all parties involved are satisfied. As presently it just finds matches and swaps whether or not the swap has been satisfied at its end

Thanks for your input, So sorry but the solution is a lot more complex than it might first seems, as I said earlier it'll need some sort of multi nested loops to handle the swaps as the wanted's cannot swap unless the person they swap with is also satisfied, so for the small sample.xls that I have attached only four staff could swap with that simple scenario.

Originally Posted by JBeaucaire

My macro seems to manage a solution for up to 10 domino swaps, it works well on the sample data. Rather than saying "I don't like this solution" explain why/how you reached that conclusion and provide the new sample data you used to reach that conclusion. Else I'll stand by the offered macro.

It should be simple for you to paste in other sets of sample data in A:C and run the macro, review the results.

So sorry, but I have posted the why/how your code doesn't work..but here it is again.
Also, I never said I didn't like your code and I am very greatful for your input regardless of whether it works or not I was only pointing out that it didn't work as you might of thought. take the following simple example data this doesn't work with your code.

Name Has Wants Swaps With
Name 1 A D Name 2
Name 2 B C Name 3
Name 3 C A Name 1
Name 4 D F Name 1

Nobody should be able to swap Yet when running your code is shows that they can swap! But in actuality nobody can, because if the swaps take place as your code suggests, then some people will end up with letters(holiday dates)at a time when they don't want to take a vacation. So all people involved in any swapping need to be happy(meaning satified) before swapping(e.g letting go of the letter they have been allocated, as they can't swap a swap after the fact). Hence...

Thanks also for your input, but again the solution is a lot more complex than it might first seems to you, as I said earlier I think it'll need some sort of multi nested loops to handle the swaps as the wanted's cannot swap unless the person they swap with is also satisfied which has a domino affect on the next swaps etc etc, so when a match is found it also needs to check that the swap its swapping with is also satified and if it doesn't then it retracts the swap, so for the small simple simplesample.xls that I have attached only four staff could swap with that scenario.

Much appreciated. see attached

Originally Posted by xladept

I've been trying to update my last post with this new code:

Please Login or Register  to view this content.

Sorry, but your sample output is way beyond my understanding. I cannot figure whats happening with it. Can you explain(simply) from your output "Who swap with who?" please!
and many thanks for remaining interested throughout.

Originally Posted by JBeaucaire

I see what happened. Try this new version.

Please Login or Register  to view this content.

thanks, I gave the new version a try, the only output I get with it is a replica(exact match) of what ever the values are in column 1. Unless I am missing something. Many thanks though for attempting to try and solve this puzzle.