• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

help with matching values in two columns

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)

PHP:
 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:
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" & 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
 

Attachments

  • sample.xlsx
    12 KB · Views: 9
Hi ,

A very interesting application.

First , can we leave your code out of the discussion ? Please clarify the following :

1. How many blocks do you want the code to cater to ?

2. How many employees do you want to put in data for ?

3. What is the input data format ? Will it be just 3 columns of data , the first column giving the employee ID , the second column giving what they have , and the third giving what they want ?

4. Do you foresee any future variations in these requirements ? If so , what can they be ?

Narayan
 
1. How many blocks do you want the code to cater to ?
Sorry, I am not sure what you mean by blocks, if you mean 2, 3, 4 X way swaps then the more match the better

2. How many employees do you want to put in data for
At most 20 staff

3. What is the input data format ? Will it be just 3 columns of data , the first column giving the employee ID, the second column giving what they have , and the third giving what they want ?

Yes, three columns of data.

4. Do you foresee any future variations in these requirements ? If so , what can they be ?
No future variations planned.
 
here's a typical email.
Hi Guys

I’m still looking for a swap in 2016 if anyone can help to match up with where my husband works.

I am Letter ‘G’ and need to swap out of these 6 weeks and would like

Letter ‘F’ dates of 26 February 2016 onwards (6wks)

Cheers

Karen
 
Hi ,

What I meant by the number of blocks is the number of letters ; you initially posted A , B , D , E , F , but in your data I see a Z. So what exactly is the maximum number of letters which could be used ?

Narayan
 
Leave dates are actually A<>H inclusive, thanks for your interest. see attached
 

Attachments

  • Leave Dates from 2014.xls
    35.5 KB · Views: 6
Hi ,

Can you confirm whether the data in your earlier file can be made into what is shown in this file ?

Narayan
 

Attachments

  • sample (6).xlsx
    8.1 KB · Views: 5
Yes, that’s looks correct, but you do understand that this data is only hypothetical data as currently there is not a register in place. So presently, we do not have any real data collected, but obviously when a register is ready to use then I imagine it’ll populate fairly quickly with all permutations of mixed combinations of leave letters. Many thanks
 
Hi ,

I don't understand your latest post ; the whole point of my earlier post was to confirm 2 points :

1. The number of blocks , which you are designating with letters ; you have confirmed that the maximum number of blocks will be 8 ( from A through H ).

2. The data will be in 2 columns , one column specifying what an employee has , and the other specifying what the employee wants.

As long as your data fulfills these 2 conditions , I don't think there should be any problem.

Narayan
 
Sorry, Yes thats correct on both points. So just to be clear taking the following simple examples, Only 3 & 5 may swap.

1 C - D
2 B - E
3 C - B
4 A - C
5 B - C

and this sample only 2, 4 & 6 may swap: =A,C,D.

1 A - B
2 C - D
3 F - A
4 A - C
5 B - E
6 D - A
 
Hi ,

See the attached file.

There are 3 output tabs named Sheet2 Random , Sheet2 Sorted and Sheet2 Sorted Restricted.

The first is with the input data in random order.

The second is where I sorted the input data before running the macro.

The third is where I sorted the input data before running the macro , and I placed a restriction that reduced the chain length of rearrangements.

It is clear that an optimal solution is difficult to get , since the input data being ordered / random can itself contribute to a great variation.

There is a constant defined in the code called MAXREARRANGEMENTS ; the limits for this are 2 and 8. The third output is with a value of 5 for this constant ; you can run the macro with different values of this constant , and see which one gives you the best result.

Narayan
 

Attachments

  • sample.xlsm
    26.5 KB · Views: 5
Hi Apologies.


Firstly I’d like to say thanks for your great efforts.

I was trying to step through your VBA, but I am a little bit lost to say the least with the output that you describe
Q: is the “Sheet2 Sorted Restricted” where someone can can swap with someone else? if so who e.g employee ID's

Also, with regards to the other worksheets you say you sorted the sheets before running the macro, did you do this manually or with code? Or ??


Many thanks from a very basic user of excel.
 
Hi ,

If you can upload a workbook which has data of the employees also , I can modify the code to have the employee IDs in addition to the block letters.

I manually sorted the data ; however , a sort is just one line of code , and can be incorporated into the present macro.

What I meant by the restricted output was that if the constant MAXREARRANGEMENTS is set to 8 , the length of the chain that is possible from the data is 8.

For example , in the Sorted tab , you can see that a chain has been suggested as follows :

B -> C swaps with C -> A swaps with A -> D swaps with D -> B swaps with B -> C swaps with C -> A swaps with A -> D swaps with D -> B

At the end of this chain , 7 rearrangements have been made , at which point , the code starts a new chain.

If instead the constant MAXREARRANGEMENTS is set to 5 , the chain will be as follows :

B -> C swaps with C -> A swaps with A -> D swaps with D -> B swaps with B -> C

at the end of which , 4 rearrangements have been made , and so a new chain is started.

Since the new chain is started from the point after the start of the previous chain , the outputs with different values of the constant MAXREARRANGEMENTS can be totally different.

If you want , I can upload a new workbook with all the outputs resulting from values of MAXREARRANGEMENTS ranging from 2 through 8.

Narayan
 
thanks for your explaination, the real data contains double digits, but I did not want to complicate things further. "A" representing all 6wks, A1 = 1 3weeks of A, A2 = 2nd 3wks of A, etc etc...but don't pay attention to this as I will adjust to suit hopefully!
 

Attachments

  • RealisticData.docx.xlsx
    18.3 KB · Views: 5
Hi ,

Sorry , but I realized that the macro is not doing what you want done ; give me some more time to revise the code.

Narayan
 
Hi ,

In your latest upload , there are only a few swaps possible , as follows

00002 E F
00007 F G
00017 G E

00003 E2 F1
00025 F1 E2

Do you have any other set of data where more swaps are possible ?

Narayan
 
Unfortunately, that’s all I got at the moment, I would need to search through all my e-mails over the past year (once I get into my workplace). It might be easier to just make some more values up with possible matches as really anybody could ask for any letter really. thanks
 
Last edited:
Hope you dont mind my input ... just looked at this as it seemed like a very interesting problem ... above what i could do anyway ... but i was haveing a look to see how messy and tricky it way ... have spotted one thing that might help ... not in your vba code but the overall problem.
attached is a work book ... on sheet one is 36 id for workers from 1 to 36 each is assigned a letter from A to F eg first 6 employees get A the next 6 get B ....
then i added there choice eg employee 2 gets A but would like C did this the whole way down and then worked out how many got what they wanted in my example 8 got what they wanted in initial assigning and a further 8 could find others willing to swap to give a total of 16 of 36 who get the holiday time of their choice.
on sheet two i then did this ... each worker sent in there 1st choice and second choice (same wokers id and same choices ) thes were then sorted automatically using a few formulas and on the first sort got 25 got there first choice holiday and on second run 5 got there second choice.

so might be a idea to get everyone to send in there choice first before the holidays are assigned ... as it seems more positive matches can be achieved this way .... it wouldnt be anmore difficult for whoever is in charge to just run the names on a spreadsheet similar to my roughly done up one to match them up as they are allready assiging employes holidays and then changing them when employees find some one to swap with
 

Attachments

  • Holiday Choise.xlsx
    18.2 KB · Views: 6
Hi John ,

Yours must be a wonderfully interconnected mind ; I hope the OP will reply to your post.

I would only like to comment that a formula such as :

=MATCH(B2:B37,$B$2:$B$37,0)

is the same as :

=MATCH(B2,$B$2:$B$37,0)

as far as your usage is concerned.

Using an array / range for the first parameter in a MATCH function is relevant in 2 cases :

1. Where the formula is entered as an array formula over multiple cells ; for instance in this case , you can select a range such as D2:D37 , type in this formula as :

=MATCH(B2:B37,$B$2:$B$37,0)

and enter it as an array formula , using CTRL SHIFT ENTER ; Excel will then insert the same formula in all the 36 cells , but will calculate the 36 outputs correctly. In this case , the range D2:D37 becomes an array , and you cannot modify or clear any one or more cells ; any action has to be carried out on the entire range , not on any sub-segment.

2. Where the MATCH function is used as a part of some other array formula , which needs to make use of the array that the MATCH function will generate.

Where you are entering the formula in one cell , as a normal formula , and copying it down to other cells down a column , this usage is the same as :

=MATCH(B2,$B$2:$B$37,0)

Second , when you upload a file , please remove any matter which is not relevant ; I find that in Sheet1 , columns D through H have formulae in them ; when I do a Trace Dependents on any of the cells in these 5 columns , Excel says there are no cells which refer to them. Probably you can confirm or clarify this.

Narayan
 
Hope you dont mind my input ...
I am lost again with who swaps with who, can you expain sheet2's output the speciifcally the 25 matches, also leave letters are assigned when they are hired (staff do give preference which most times is usually accomodated) and remain with them throughout there employment, they are now allowed to change this letter ever. we are only allowed One swap within the current year if we can find someone willing to swap. Then when the next year comes they are back to what they were given at hire date. Also, dates are set in stone to match each leave letter some 6yrs in advance. At the moment were are up to 2021. see attached file
 

Attachments

  • Leave Dates from 2014 - Copy.xls
    36.5 KB · Views: 9
Hi John ,

Yours must be a wonderfully interconnected mind ; I hope the OP will reply to your post.

I would only like to comment that a formula such as :

=MATCH(B2:B37,$B$2:$B$37,0)

is the same as :

=MATCH(B2,$B$2:$B$37,0)

as far as your usage is concerned.

Using an array / range for the first parameter in a MATCH function is relevant in 2 cases :

1. Where the formula is entered as an array formula over multiple cells ; for instance in this case , you can select a range such as D2:D37 , type in this formula as :

=MATCH(B2:B37,$B$2:$B$37,0)

and enter it as an array formula , using CTRL SHIFT ENTER ; Excel will then insert the same formula in all the 36 cells , but will calculate the 36 outputs correctly. In this case , the range D2:D37 becomes an array , and you cannot modify or clear any one or more cells ; any action has to be carried out on the entire range , not on any sub-segment.

2. Where the MATCH function is used as a part of some other array formula , which needs to make use of the array that the MATCH function will generate.

Where you are entering the formula in one cell , as a normal formula , and copying it down to other cells down a column , this usage is the same as :

=MATCH(B2,$B$2:$B$37,0)

Second , when you upload a file , please remove any matter which is not relevant ; I find that in Sheet1 , columns D through H have formulae in them ; when I do a Trace Dependents on any of the cells in these 5 columns , Excel says there are no cells which refer to them. Probably you can confirm or clarify this.

Narayan
Thanks again for remaining with us on this issue, are you suggesting that I swap the formulas for they suggested ones and if I do this will the code work for me as needed. not that I know what exactly they do specifically. It's way beyond my understanding of excel...Many thanks
 
Back
Top