syntax for annual leave roster

[gint32]

Look at a simple example

Staff A has vacation period A
Staff B has vacation period B



Staff A wants to swap his entire block for period B
Staff B wants to swap his entire block for period A

This is *not*, in terms of programming, a 1 for 1 examination

This is

Staff A - Period A, 1st Half TO Period B, 1st Half
Staff A - Period A, 1st Half TO Period B, 2nd Half
Staff A - Period A, 2nd Half to Period B, 1st Half
Staff A - Period A, 2nd Half to Period B, 2nd Half

I am no programmer, but surely with the simple example you give.

If
Staff A has vacation period A
Staff B has vacation period B
Staff A wants to swap his entire block for period B
Staff B wants to swap his entire block for period A
There is no sense and evaluating this any further so code should loop out, then after removing these two from the table to another table Maketable and update table and sending to a report or print for processing (as it will not help anyone else that’s looking to swap keeping them in the), then both staff are happy and that’s that, code should then loop back into looking at the next records for the next easy swaps
and same goes for anyone else that has this simple scenario.

Also If...
Staff A has vacation period A1(=1^st3wk block) to swap and that’s all he has got.
and Staff B has vacation period B2(=2nd 3wk block) to swap and that’s also all he has got.
Staff A wants to swap his 1^st block for staff B’s 2nd block
and Staff B is willing, then they swap.
Again, There is no sense in evaluating that table any further at that point so the code should loop out again, after removing these two from the table and sending to a report, print and or Make-table or print for processing as it will not help anyone else that’s looking to swap, both of these staff are happy too.
Each time the table or register reduces in size.
So once these swap occur, whats left in the register/table would then become more difficult to deal with.

Like the following:

Staff1	A	B	Staff 1 swaps with 3
Staff2	D	C	Staff 2 swaps with 4
Staff3	B	D	Staff 3 swaps with 2
Staff4	C	A	Staff 4 swaps with 1

But the main thing as I was talking about earlier is as long as table A contents match table B contents equally then they those staff that are involved all get together and swap, therfore reducing the table to almost nothing.

[rpeare]

Programming at my level can not make leaps of logic. Code has to cycle through a series of records, determine if it can find at least 1 match, if it can in count the original record as matched and mark the 'matching' record as a match found as well, then move on to the next non matched record. This is done by opening recordsets and cycling through them. In terms of processing the data the data needs to be in the lowest common break period (3 weeks) and every combination of periods needs to be considered. I don't really know how to explain it any more than that.

Here's a slightly different example so you don't get hung up on full periods vs half periods.

Staff A has Vacation Period A
Staff B has vacation Period B
Staff C has Vacation Period C

Staff A wants to trade for the 1st part of Period B and the 2nd Part of Period C

Staff B wants to trade for the 2nd part of Period A and the 1st Part of Period C
Staff C wants to trade for the 2nd Part of Period B and the 1st part of Period A

To the human eye this is an easy match up, but you CAN NOT apply a visual rule to computer code. In terms of processing records this is actually a matrix like:

Staff	Period	Half	PeriodTo	Half
A	A	1	B	1
A	A	2	B	1
A	A	1	C	2
A	A	2	C	2
B	B	1	A	2
B	B	2	A	2
B	B	1	C	1
B	B	2	C	1
C	C	1	B	2
C	C	2	B	2
C	C	1	A	1
C	C	2	A	1

When you process this recordset you are 100% correct in saying that a vast majority of the items will be processed and discarded but the code still needs to cycle through the records in ever permutation to find the best match, there is no intuitive leap the computer can make. In the case of this example it doesn't much matter how the records are processed you're going to end up with the same number of maximum match records because it doesn't really matter what order they are processed in, but that is not the case with your real data. With a real set of data you will end up with match requests that can not be satisfied. So let's say you throw in Staff D wants a specific trade of the first part of D for the 2nd part of E. The reason I keep stressing the permutations is that the computer will not intuitively know this request can not be met, it needs to try the Staff D request as the first through 13th possible processing order and every combination of records. Now what you could do to limit your processing is do some 'pre-processing' like mark any 'unmatchable' records before you do the bulk of your testing (below) but you still then need to cycle through the remaining 12 records in every possible order to determine if the best match. You can limit your exposure here by marking 'like' records (in this case the swapping Staff/Period/Half) with a processing flag as you go but you still have to evaluate each record not only for the 'trade from' but the 'trade to' to find out if there are any remaining swaps that can be satisfied. Below is an example of how your code would process them in the order I listed. for *1* of the possible 11! combinations of records it would have to process in order to determine what the best match was. There are things you can do to mitigate the repeated examination of records that may have been excluded due to a match being found but this is a highly, highly complex set of code you would need. If anyone else would like to chime in on this thread please do because maybe I am missing something in your description and I'm definitely having a problem explaining the programming required to make this work.

ProcessingOrder	Staff	Period	Half	PeriodTo	Half	Processing	ProcessingOrderMatch
1	A	A	1	B	1	Match Found	5
2	A	A	2	B	1	Trade TO match duplicate
3	A	A	1	C	2	Trade FROM match duplicate
4	A	A	2	C	2	Match Found	10
5	B	B	1	A	2	Match Found	1
6	B	B	2	A	2	Trade TO match duplicate
7	B	B	1	C	1	Trade FROM match duplicate
8	B	B	2	C	1	Match Found	9
9	C	C	1	B	2	Match Found	8
10	C	C	2	B	2	Match Found	4
11	C	C	1	A	1	Trade FROM *AND* Trade TO match duplicate
12	C	C	2	A	1	Trade FROM *AND* Trade TO match duplicate
13	D	D	1	E	2	No Possible Matches

[gint32]

I always thought for every problem there was a solution, thanks for your detailed explanation, sounds like a brick wall then. Maybe I should just display matches visually and let HR select from a basic display of records.

[rpeare]

There is a solution. It's just, in this particular case, the computing power required makes it very difficult. I'm still playing around with code trying to find a way to make it workable but I am not tremendously hopeful.

[gint32]

There is a solution. It's just, in this particular case, the computing power required makes it very difficult. I'm still playing around with code trying to find a way to make it workable but I am not tremendously hopeful.

many thanks, any solution will be better than no solution eg: perhaps setting for a 3Xway swap MaX.

[gint32]

just checking to see if you managed to find a solution, I really think the best way to resolve the problem is to firstly/remove all the direct 2X way swaps.

1. Staff 1 has “A” wants “B”
2. Staff 5 has “B” Wants “A” then remove all 2 way matches

Then Search for all combinations of matches in the following way (not that I know how to do this in VBA): take this scenario

A-D
B-A
C-F
E-G
C-H
F-A
D-B
G-C

Presently, It would be difficult to know “who can swap with who” unless you run through each value line by line and see check for this which is very complicated.
But, I think there is a much easier way to find out really quickly.
Which is to use code to search for matches such as the following stolen from Excel
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

Then from the results above search for matching values that are in each column that are on the same row, meaning Column A = (A,B,D) & on the same rows we have (D,B.A) and if both columns are true, then we know we can safely swap knowing that each person is happy.

[rpeare]

I wasn't able to find a way to properly address the 'find the best possible match' which was your stated goal without an undue burden of processing time. There are a number of ways you can try to shortcut trying every permutation of possible matches but they will not give you the best match 100% of the time, particularly as your number of potential segment trades increases. What you are suggesting will work fine for the example you created, but it may not necessarily work for all situations, and what you're looking for is a reliable solution for all circumstances.

I don't use excel very much so I'm not entirely sure what your formuls is doing because I'm getting an error on the replace formula, perhaps supply a spreadsheet with this code working and I can take a look.

[gint32]

RealisticData1.zip
Hi, the following file get some of the data but not all, and unfortunately I don't know enough about VBA. I would love this to work in MS Access which I understand a little better, let me know what you think. thanks

[gint32]

Hi, Was the spreadsheet I uploaded of any value to you? or would you like to have a look at some VBA that nearly does the job(excel). After sorting a few hundred peices of test data manually in excel, I have now come to the conclusion that searching for two way swaps then 3 way, then 4Xway and so on was wrong. As it should be the other way round, as I’ll show you why further on. Take the following test data. Say we had matched the two first 2Xway swap using AC&CA. With the CA pair now used, then the corresponding 21X way swap could not have been able to take place. Also, I have noticed that (after pairing the way I have done similar to below) then if you match any letter in Col A with any letter after that in Col B then all involved can safely swap as is the same the other way round ColB with anything matching in ColA from that point forward. Anyway, as you can see from the below sample data, most of the letters can swap in various places throughout(this is why I think VBA code should instead look for the greatest number of a swap possible, Although, from the sample data there are many many possible swaps within the below solution, but none of these would actually use up all of these letters. So only the matches that contain the most number of swaps is in my opinion the best solution. I.e. the more people/leave-letters involved then the more people are satisfied and are happy. So to start with I need to get the code to firstly remove all entries that are in one column that are not in the other, then to do the same the other way round. Sort then do the same again. until completely satified that there are no more unwanted values within each columns. I think the only way to figure out how to solve this, is to actually try and sort matches out manualy firstly, then and only then will the tasks be clear coding wise...Oh I wish I could program as I know what I need to do but can't do this via VBA. if you have the time then please have look at my recorded macro and see how I set about this manually (Step by Step) sub Sort(). see attached

,
A < >	C	……..		A<>	B
A < >	B	……..		B< >	E
A< >	D	……..		E< >	C
A< >	D	……..		C< >	D
A< >	G	……..		D< >	E
B< >	E	……..		E< >	H
B< >	A	……..		H< >	F
C< >	D	……..		F< >	G
C< >	A	……..		G< >	H
C< >	E	……..		H< >	C
D< >	E	……..		C< >	A
D< >	C	……..		A< >	D
D< >	B	……..		D< >	C
E< >	C	……..		C< >	E
E< >	H	……..		E< >	A
E< >	A	……..		A< >	D
F< >	G	……..		D< >	B
F< >	A	……..		B< >	A
G< >	H	……..		A< >	G
G< >	F	……..		G< >	F
H< >	F	……..		F< >	A
H< >	C	……..

The above 21X way swap would not have been possible had we used the 2Xway swap AC<for>CA firstly. As we needed CA to be included in the large swap.

[gint32]

Do you need more info? to be able to better understand

[rpeare]

I haven't actually looked at this in a while but I did some thinking. It might be worth taking this problem to a professor at a local university or statistician/actuary.

This is really a problem with combinatorics that I do not remember well enough from college to tackle in an efficient way.

If the goal is to find the best possible match the problem hasn't changed. There are tons of solutions you can find for specific instances but to reliably get the best possible match 100% of the time there are some heavy duty calculations that will need to go on. If you stick to swapping an entire vacation period with none of the first half/second half it becomes a markedly easier function to handle but would still be incredibly complex.

For instance your example in post 24, assuming those were all trades for the entire 6 week period you would still have 21! possible combinations (or 51,090,942,171,709,440,000 possible combinations to try). If you start fracturing that into 1st half 2nd half combinations you end up with a lot more.

What you might be able to do, for instance is if you find a match for period you remove both periods from consideration for any further matches but that involves a lot of opening and closing recordsets which may defeat the attempted efficiency of processing one recordset