Link to home
Start Free TrialLog in
Avatar of graysoc
graysocFlag for United States of America

asked on

VBA to Run Job Preferences

All,

I'm trying to get the following script to run.  I have an excel doc I can post.  Basically, this works fine, except that it needs to look multiple times at now many slots are open.  Now it just runs once.

How can I make it run multiple times, and look for multiple openings on the analysis tab?

Thanks!

 Scrubbed-Version.xlsm
Sub FillBids()

Dim dctRoute As Dictionary
Set dctRoute = New Dictionary

Dim arrRoute(50) As String
Dim arrMax(50) As Integer
Dim arrCount(50) As Integer

Application.ScreenUpdating = False

numBidRows = Sheets("ALL BIDS").Range("A10000").End(xlUp).Row

For Each c In [myRoutes]
  
  x = x + 1
  
  dctRoute.Add c.Value, x
  arrRoute(x) = c.Value
  arrMax(x) = c.Offset(0, 10).Value ' this is where I get the total number of people
                                '    I'm trying to fill
Next c

myTotal = 0


For j = 2 To numBidRows


Application.StatusBar = "Processing seniority position " & Sheets("ALL BIDS").Cells(j, 1).Value & " ..."
  
  For k = 1 To 14 'num of bids per person
    
    thisRoute = Sheets("ALL BIDS").Cells(j, k + 4).Value
    
    If thisRoute <> "NULL" Then
    
      If arrCount(dctRoute(thisRoute)) < arrMax(dctRoute(thisRoute)) Then
        
        arrCount(dctRoute(thisRoute)) = arrCount(dctRoute(thisRoute)) + 1
        
        For m = 1 To 19
          Sheets(thisRoute).Cells(arrCount(dctRoute(thisRoute)) + 1, m + 1) = Sheets("ALL BIDS").Cells(j, m).Value
        Next m
        
        
        Exit For
        
      End If
    
    Else
      
      'Sheets(thisRoute).Range(Cells(j, 1), Cells(j, 15)).Interior.ColorIndex = 3 ' this would do the whole row
      Sheets("ALL BIDS").Cells(j, 4).Interior.ColorIndex = 3   ' this does just the name column
      
    End If
    
  Next k
  
Next j

Application.StatusBar = False
Application.ScreenUpdating = True

End Sub

Open in new window

Avatar of RobSampson
RobSampson
Flag of Australia image

Hi, at first glance, it looks like it might be the Exit For that's kicking it out, but I can't really understand what the code is doing.  Can you elaborate a bit more on what it's currently doing, and what you want it to achieve?

Regards,

Rob.
Avatar of graysoc

ASKER

This code works just fine.  If you download the excel, you'll see that it processes perfectly.  

Unfortunately, it processes just one time, and doesn't take into account the current position, and vacancies in each position.  

It is complicated, and it will take a lot to explain online, but I can try if you want to give it a shot.  

I just need to see how to make it run more than once, taking into account the number of vacancies.
What would it do if it ran more than once?  Could you add a loop around it like

Sub FillBids()
For intIteration = 1 To 10
 ......
 ......
Next
End Sub
Avatar of graysoc

ASKER

No, because the result will  be the same for each iteration.  The file has to look at the number of vacancies (positive or negative) for the first run, then run for the next category, and the next, etc. until it has run once for each category.  

This, in a nutshell, is how it needs to work:

If a person's current position is M1 J1 C2, and their first choice is M1 J1 C1, but there are negative vacancies in that position, then they DO NOT get to leave their current position to go there.  Right now, if they are seniority number 1, when it runs they get their first choice disregarding whether there are vacancies in that position.  That's what is wrong with the code.  It looks ONLY at seniority and the first choice, and doesn't ever look at current position and vacancies on the analysis tab.

Now, to add complication, if someone from M1 J1 C1 leaves to go to a position there IS a vacancy in, then he leaves a resultant vacancy in that position, and the dude who bid for it CAN move to it in the next run.  That's why it needs to run once per position, so the computer can calculate the resultant vacancies for the next group, and determine if anyone gets to leave their current positions to go there.

I know that sounds complicated, and it is to some extent, but it should be pretty simple to get it to work oonce it is looking in the right place.

I just need a point in the right direction on how to get it to look at the vacancies and current position.
OK, I'm getting closer to understanding what you're doing, but I can't figure out how you determine vacancies?  Is it the New Total column on the analysis tab (which you store in arrMax)?

How do you calculate total existing positions filled?  Just by the current position?

This does sound complicated though.  I think what might get you closer is determining whether you have successfully allocated a bid (by writing the destination to column D maybe), and then looping through all rows until each bid has been allocated.

Eg,

Pass one determines whether column D is empty, and if so, reads the bids until it finds an available space, then writes that allocated space to column D. This is done for every bid.  As vacancies arise, you will need to subtract one from the position that was left as well.

Subsequent passes will then take into account the subtracted values, and skip over any rows that have already been allocated, and try to allocate the rest.

If you're still stuck, I'll have a crack at coding it, but it might take me a while to get it right.

Can I ask, what does this bit do?

                        For m = 1 To 19
                            Sheets(thisRoute).Cells(arrCount(dctRoute(thisRoute)) + 1, m + 1) = Sheets("ALL BIDS").Cells(j, m).Value
                        Next m

It looks like it would write the copy of the bidding preference to the route sheet. Is that for historical reasons?

Rob.
Avatar of graysoc

ASKER

Rob,

The way it works currently is that I manually come up with the "New Total" in column L by trying to get the "future On-line" column H total to match the "desired on-line" column F total.  Because some people are on leave, or in management, they may get what they bid, but not actually work that job, so I have to manually account for that and increase the number desired until the future on-line number matches.  Instead of me manually putting in that number, it should instead run until the future on-line and desired on-line numbers match, as long as it is taking into account the "off" status from the all bids page (column S).  It should still give those with something other than NULL a bid, but they shouldn't count towards the total.

The TOAL REDUCTION column is just showing how many spots are open for each position.  The negative numbers show that some of thos jobs are going away, and vice versa.  The ones that are negative are the ones that should not let someone NEW (current status) bid into them until/unless there is a positive number.  

It seems like you have a handle on what is going on, and I appreciate the help.  

The bit that you asked about copies the bidding preferences to the sheets, and yes, it is so everyone can double check at the end to be sure they were bid properly.

Please let me know if that helps!
I'll take another look at this one tomorrow.  Hopefully I can get something functional to iterate through this.

Rob.
OK, I'm very very close to being lost by the required logic and workflow of this, but here's one attempt.

What happens is it clears column D on the ALL BIDS sheet, and then records the maximum (NEW TOTAL) for a route from column L on ANALYSIS, and records the TOTAL REDUCTION from column J, which is used to determine whether a new position can be filled there.  I also record the current position for each bidder, so we can reduce that allocation from the route they left.

It then iterates the entire bidding table up to 14 times, in an attempt to make sure everyone has been allocated one of their bids.

I have commented out any lines that actually change or write data, except for writing the successful bid to Column D on the ALL BIDS sheet.  At the end you are presented with a message box that states what the new TOTAL REDUCTION value would be if the code were to update that column.

Regards,

Rob.
Sub FillBids()

Dim dctRouteMax As Dictionary
Set dctRouteMax = New Dictionary
Dim dctRouteReduction As Dictionary
Set dctRouteReduction = New Dictionary

Application.ScreenUpdating = False

numBidRows = Sheets("ALL BIDS").Range("A10000").End(xlUp).Row

strOldReductions = "Old reductions were:"
For Each c In [myRoutes]
   dctRouteMax.Add c.Value, c.Offset(0, 10).Value ' this is where I get the total number of people I'm trying to fill
   dctRouteReduction.Add c.Value, c.Offset(0, 8).Value ' this is where I get the total reduction for each route
   strOldReductions = strOldReductions & vbCrLf & c.Value & ": " & c.Offset(0, 8).Value
Next c

blnNotAllAllocated = True
intAttempts = 0

Sheets("ALL BIDS").Range("D2:D10000").Clear

While blnNotAllAllocated = True And intAttempts < numBidRows * 14
    blnNotAllAllocated = False
    For j = 2 To numBidRows
    
      intAttempts = intAttempts + 1
      
      currentPosition = Sheets("ALL BIDS").Cells(j, 3).Value
      
      Application.StatusBar = "Pass " & Int(intAttempts / numBidRows) + 1 & " of 14 - Processing seniority position " & Sheets("ALL BIDS").Cells(j, 1).Value & " ..."
      
      If Sheets("ALL BIDS").Cells(j, 4).Value = "" Then blnNotAllAllocated = True
      
      For k = 1 To 14 'num of bids per person
        
        thisRoute = Sheets("ALL BIDS").Cells(j, k + 4).Value
        
        If thisRoute <> "NULL" Then
        
          If dctRouteReduction(thisRoute) < 0 Then
            
            dctRouteReduction(thisRoute) = dctRouteReduction(thisRoute) + 1
            dctRouteReduction(currentPosition) = dctRouteReduction(currentPosition) - 1
            
            'For m = 1 To 19
            '  Sheets(thisRoute).Cells(dctRouteMax(thisRoute) + 1, m + 1) = Sheets("ALL BIDS").Cells(j, m).Value
            'Next m
                    
            Sheets("ALL BIDS").Cells(j, 4).Value = Sheets("ALL BIDS").Cells(1, k + 4).Value & ": " & thisRoute
            
            Exit For
            
          End If
        
        Else
          
            'Sheets(thisRoute).Range(Cells(j, 1), Cells(j, 15)).Interior.ColorIndex = 3 ' this would do the whole row
            'Sheets("ALL BIDS").Cells(j, 4).Interior.ColorIndex = 3   ' this does just the name column
          
        End If
        
      Next k
      
    Next j
Wend

strNewReductions = "New reductions are:"
For Each strR In dctRouteReduction
    strNewReductions = strNewReductions & vbCrLf & strR & ": " & dctRouteReduction(strR)
Next

MsgBox strNewReductions & vbCrLf & vbCrLf & strOldReductions

Application.StatusBar = False
Application.ScreenUpdating = True

End Sub


Sub ClearTabs()

Sheets("All BIDS").Cells.Interior.ColorIndex = 0
 
For Each c In [myRoutes]
  Sheets(c.Value).Range("A2:VV1000").ClearContents
Next c

End Sub

Open in new window

Avatar of graysoc

ASKER

It's not quite there yet.  

A perfect example is # 185.  His first bid is M2 J1 C3, but he got his current position.  He should have gotten his first choice, by far.  I cannot really follow how the processing is happening now.

How can I help you understand the logic?  I'll try another stab...

The system should go through and process everyone's first choice, then second, then third for each line until there are no more positions open, as determined by the (new total).  The only positions to worry about not posting to are the negative ones.  (The ones with a reduction.)  In those cases, no one should the assigned to them who isn't already in that job unless there was an opening due to someone else bidding out of the job.

For instance, if I'm in a job that has a reduction, but I bid out of it, then there is one fewer reduction to worry about.  (-25 + me = -24).  If 26 people bid OUT of the position, and one person wants in it, they can bid into it.  Otherwise, they have to go to another bid preference, or stay where they are, or in the worst case get displaced to a job that has openings.

So basically, if the total reduction column is negative, and someone puts that job as a preference, it needs to ignore it until it is positive.  People currently in that position stay in it, unless they bid out of it into a position that is open.

It was easier to analyse if the system was working properly when it posted to all of the sheets.  

Is that better?

Let me know if I can help in any way.  I think it is getting closer, but the VBA is well over my head now.



OK, so with #185 as an example, his first bid is M2 J1 C3, but, the way I understood it, if the total reduction is greater than zero (which for M2 J1 C3 is 56 in column J), then they cannot go there as there's no room.....is that backwards?  Given you've said this: "The only positions to worry about not posting to are the negative ones" - I think that is backwards....easy to change though....

Currently the bidding is processed from left to right, per person, rather than top to bottom, per bid, so I'll need to transpose that logic.

I'll put the posting back to the sheets, but I think the data might be inconsistent in them at the moment.  For example, the M2 J1 C3 sheet has data for M2 J1 C1.  And I won't want to write there until the totals on the ANALYSIS tab are being updated, because the macro uses the New Total value as the row to write to on each route sheet.

One thing that may not work as designed.....you say this:
"If 26 people bid OUT of the position, and one person wants in it, they can bid into it."

But, when we transpose it to top to bottom, and it's not until Bid4 that a position becomes open, but someone bid for it at Bid2, they're not going to get it, because that bid has been processed.  Is that OK?  That would be very hard to overcome I think.

Regards,

Rob.
Here's the code with the reversal of the TOTAL REDUCTION behaviour....I'll transpose it shortly.

Regards,

Rob.
Sub FillBids()

Dim dctRouteMax As Dictionary
Set dctRouteMax = New Dictionary
Dim dctRouteReduction As Dictionary
Set dctRouteReduction = New Dictionary

Application.ScreenUpdating = False

numBidRows = Sheets("ALL BIDS").Range("A10000").End(xlUp).Row

strOldReductions = "Old reductions were:"
For Each c In [myRoutes]
   dctRouteMax.Add c.Value, c.Offset(0, 10).Value ' this is where I get the total number of people I'm trying to fill
   dctRouteReduction.Add c.Value, c.Offset(0, 8).Value ' this is where I get the total reduction for each route
   strOldReductions = strOldReductions & vbCrLf & c.Value & ": " & c.Offset(0, 8).Value
Next c

blnNotAllAllocated = True
intAttempts = 0

Sheets("ALL BIDS").Range("D2:D10000").ClearContents

While blnNotAllAllocated = True And intAttempts < numBidRows * 14
    blnNotAllAllocated = False
    For j = 2 To numBidRows
    
      intAttempts = intAttempts + 1
      
      currentPosition = Sheets("ALL BIDS").Cells(j, 3).Value
      
      Application.StatusBar = "Pass " & Int(intAttempts / numBidRows) + 1 & " of 14 - Processing seniority position " & Sheets("ALL BIDS").Cells(j, 1).Value & " ..."
      
      If Sheets("ALL BIDS").Cells(j, 4).Value = "" Then blnNotAllAllocated = True
      
      For k = 1 To 14 'num of bids per person
        
        thisRoute = Sheets("ALL BIDS").Cells(j, k + 4).Value
        
        If thisRoute <> "NULL" Then
        
          If dctRouteReduction(thisRoute) > 0 Then
            
            dctRouteReduction(thisRoute) = dctRouteReduction(thisRoute) - 1
            dctRouteReduction(currentPosition) = dctRouteReduction(currentPosition) + 1
            
            'For m = 1 To 19
            '  Sheets(thisRoute).Cells(dctRouteMax(thisRoute) + 1, m + 1) = Sheets("ALL BIDS").Cells(j, m).Value
            'Next m
                    
            Sheets("ALL BIDS").Cells(j, 4).Value = Sheets("ALL BIDS").Cells(1, k + 4).Value & ": " & thisRoute
            
            Exit For
            
          End If
        
        Else
          
            'Sheets(thisRoute).Range(Cells(j, 1), Cells(j, 15)).Interior.ColorIndex = 3 ' this would do the whole row
            'Sheets("ALL BIDS").Cells(j, 4).Interior.ColorIndex = 3   ' this does just the name column
          
        End If
        
      Next k
      
    Next j
Wend

strNewReductions = "New reductions are:"
For Each strR In dctRouteReduction
    strNewReductions = strNewReductions & vbCrLf & strR & ": " & dctRouteReduction(strR)
Next

MsgBox strNewReductions & vbCrLf & vbCrLf & strOldReductions

Application.StatusBar = False
Application.ScreenUpdating = True

End Sub


Sub ClearTabs()

Sheets("All BIDS").Cells.Interior.ColorIndex = 0
 
For Each c In [myRoutes]
  Sheets(c.Value).Range("A2:VV1000").ClearContents
Next c

End Sub

Open in new window

Avatar of graysoc

ASKER

It still needs to bid from left to right.  To determine if there is an opening, it needs to run per opening, I think.  I haven't really worked all of that out yet.

As far as the negative ones, the people who are already there get to stay there, except for the bottom X number of people.

Is there any way we could talk on the phone?  I would like to discuss to see if we can work out the details of the bid process.  We can still work on the logic here.  I just feel that we need to discuss the bid.

In the current iteration, number 185 should have gotten his first choice.  I'm not following the logic as to why he didn't.  He would be nearly number 1 in that job, since he is so senior.

I think a good phone call will solve much of the problems.
Oh, I just realised I was reprocessing the already allocated bids....please test this.

It looks like our time zones are vastly different....it's 11:30am here on Thursday.  I can't make international calls either.....

When I transpose it, it will still process left to right, but it process top to bottom first.

Eg, currently each person has all bids processed before going to the the next person.  When I transpose, each person will have only one bid processed at a time, so bid1 for every person, then every bid2, bid3 etc.
So, currently it's left to right, then top to bottom, but it sounds like you want it top to bottom, then left to right.

Regards,

Rob.
Sub FillBids()

Dim dctRouteMax As Dictionary
Set dctRouteMax = New Dictionary
Dim dctRouteReduction As Dictionary
Set dctRouteReduction = New Dictionary

Application.ScreenUpdating = False

numBidRows = Sheets("ALL BIDS").Range("A10000").End(xlUp).Row

strOldReductions = "Old reductions were:"
For Each c In [myRoutes]
   dctRouteMax.Add c.Value, c.Offset(0, 10).Value ' this is where I get the total number of people I'm trying to fill
   dctRouteReduction.Add c.Value, c.Offset(0, 8).Value ' this is where I get the total reduction for each route
   strOldReductions = strOldReductions & vbCrLf & c.Value & ": " & c.Offset(0, 8).Value
Next c

blnNotAllAllocated = True
intAttempts = 0

Sheets("ALL BIDS").Range("D2:D10000").ClearContents

While blnNotAllAllocated = True And intAttempts < numBidRows * 14
    blnNotAllAllocated = False
    For j = 2 To numBidRows
        intAttempts = intAttempts + 1
        currentPosition = Sheets("ALL BIDS").Cells(j, 3).Value
        Application.StatusBar = "Pass " & Int(intAttempts / numBidRows) + 1 & " of 14 - Processing seniority position " & Sheets("ALL BIDS").Cells(j, 1).Value & " ..."
        If Sheets("ALL BIDS").Cells(j, 4).Value = "" Then
            blnNotAllAllocated = True
            For k = 1 To 14 'num of bids per person
                thisRoute = Sheets("ALL BIDS").Cells(j, k + 4).Value
                If thisRoute <> "NULL" Then
                    If dctRouteReduction(thisRoute) > 0 Then
                        dctRouteReduction(thisRoute) = dctRouteReduction(thisRoute) - 1
                        dctRouteReduction(currentPosition) = dctRouteReduction(currentPosition) + 1
                        'For m = 1 To 19
                        '    Sheets(thisRoute).Cells(dctRouteMax(thisRoute) + 1, m + 1) = Sheets("ALL BIDS").Cells(j, m).Value
                        'Next m
                    
                        Sheets("ALL BIDS").Cells(j, 4).Value = Sheets("ALL BIDS").Cells(1, k + 4).Value & ": " & thisRoute
                        Exit For
                    End If
                Else
                    'Sheets(thisRoute).Range(Cells(j, 1), Cells(j, 15)).Interior.ColorIndex = 3 ' this would do the whole row
                    'Sheets("ALL BIDS").Cells(j, 4).Interior.ColorIndex = 3   ' this does just the name column
                End If
            Next k
        End If
    Next j
Wend

strNewReductions = "New reductions are:"
For Each strR In dctRouteReduction
    strNewReductions = strNewReductions & vbCrLf & strR & ": " & dctRouteReduction(strR)
Next

MsgBox strNewReductions & vbCrLf & vbCrLf & strOldReductions

Application.StatusBar = False
Application.ScreenUpdating = True

End Sub


Sub ClearTabs()

Sheets("All BIDS").Cells.Interior.ColorIndex = 0
 
For Each c In [myRoutes]
  Sheets(c.Value).Range("A2:VV1000").ClearContents
Next c

End Sub

Open in new window

Avatar of graysoc

ASKER

That is better.  Number 186 should be M2 J1 C1.  It is their current position, and their first choice.  They only should go to M2 J1 C3 if they cannot hold their current position, and there are plenty of positions in their current position.

No NEW positions should be awarded in a position with a reduction, but anyone currently in the position should still hold it, with the exception of the few people at the bottom who may be displaced.

The bidding should go from number one (first bid, second bid, third bid, etc.) then number 2 (first bid, second bid, etc.) And so on.  The original way it worked was fine, except that it didn't take into account current position and it also didn't take into account the reductions in position.  

I really really think it should process M1 J1 C1 first for all people, then process M2 J1 C1, for all people, taking into account the number of people who bid off of that other position, etc.  

It should process at least once per position.  That gives each iteration the ability to process taking into account the previous processing adjustments to total reductions.

Does that help?
OK, so you want it to keep processing all bids for each person until going to the next person.....that's the original way it worked, so I'll keep working off that.

So this post is just an aside, for something else for you to look at.  This processes Bid1 for everyone, then Bid2, then Bid3, etc, until every person has been allocated.  It had to iterate the entire bids twice when I ran it.

Most people retained there current position, but look at number 10.  He moved in to M2 J1 C3 (Bid 1) because there was a spot available for his first bid, so I assume that's right.

Now, another thing to clarify, when I go back to the original method:
"anyone currently in the position should still hold it"
Does that mean anyone who has Bid1 the same as their current position, that they should hold it no matter what....even if the TOTAL REDUCTION is negative?  It currently doesn't work that way. It's currently designed to bring all reductions back to zero as close as possible.

Regards,

Rob.
Sub FillBids()

Dim dctRouteMax As Dictionary
Set dctRouteMax = New Dictionary
Dim dctRouteReduction As Dictionary
Set dctRouteReduction = New Dictionary

Application.ScreenUpdating = False

numBidRows = Sheets("ALL BIDS").Range("A10000").End(xlUp).Row

strOldReductions = "Old reductions were:"
For Each c In [myRoutes]
   dctRouteMax.Add c.Value, c.Offset(0, 10).Value ' this is where I get the total number of people I'm trying to fill
   dctRouteReduction.Add c.Value, c.Offset(0, 8).Value ' this is where I get the total reduction for each route
   strOldReductions = strOldReductions & vbCrLf & c.Value & ": " & c.Offset(0, 8).Value
Next c

blnNotAllAllocated = True
intAttempts = 0

Sheets("ALL BIDS").Range("D2:D10000").ClearContents

While blnNotAllAllocated = True And intAttempts < numBidRows * 14
    blnNotAllAllocated = False
    For k = 1 To 14
        For j = 2 To numBidRows
            Application.StatusBar = "Processing Bid " & k & " of 14 - Processing seniority position " & Sheets("ALL BIDS").Cells(j, 1).Value & " ..."
            intAttempts = intAttempts + 1
            currentPosition = Sheets("ALL BIDS").Cells(j, 3).Value
            If Sheets("ALL BIDS").Cells(j, 4).Value = "" Then
                blnNotAllAllocated = True
                thisRoute = Sheets("ALL BIDS").Cells(j, k + 4).Value
                'MsgBox thisRoute & ": " & j & "," & k + 4
                If thisRoute <> "NULL" Then
                    'MsgBox j & ", " & k + 4 & ": " & thisRoute & " spaces left " & dctRouteReduction(thisRoute)
                    If dctRouteReduction(thisRoute) > 0 Then
                        dctRouteReduction(thisRoute) = dctRouteReduction(thisRoute) - 1
                        dctRouteReduction(currentPosition) = dctRouteReduction(currentPosition) + 1
                        'For m = 1 To 19
                        '    Sheets(thisRoute).Cells(dctRouteMax(thisRoute) + 1, m + 1) = Sheets("ALL BIDS").Cells(j, m).Value
                        'Next m
                    
                        Sheets("ALL BIDS").Cells(j, 4).Value = Sheets("ALL BIDS").Cells(1, k + 4).Value & ": " & thisRoute
                        'Exit For
                    End If
                Else
                    'Sheets(thisRoute).Range(Cells(j, 1), Cells(j, 15)).Interior.ColorIndex = 3 ' this would do the whole row
                    'Sheets("ALL BIDS").Cells(j, 4).Interior.ColorIndex = 3   ' this does just the name column
                End If
            End If
        Next j
    Next k
Wend

strNewReductions = "New reductions are:"
For Each strR In dctRouteReduction
    strNewReductions = strNewReductions & vbCrLf & strR & ": " & dctRouteReduction(strR)
Next

MsgBox strNewReductions & vbCrLf & vbCrLf & strOldReductions

Application.StatusBar = False
Application.ScreenUpdating = True

End Sub


Sub ClearTabs()

Sheets("All BIDS").Cells.Interior.ColorIndex = 0
 
For Each c In [myRoutes]
  Sheets(c.Value).Range("A2:VV1000").ClearContents
Next c

End Sub

Open in new window

You mentioned in post ID 35089850 that you think it should process once per position.  This would mean iterating through the routes, then be rows, then by bids, adding a bit more complexity to it.  Let me know which code ID you want to run off, and what needs fixing.

Rob.
Avatar of graysoc

ASKER

Now, another thing to clarify, when I go back to the original method:
"anyone currently in the position should still hold it"
Does that mean anyone who has Bid1 the same as their current position, that they should hold it no matter what....even if the TOTAL REDUCTION is negative?  It currently doesn't work that way. It's currently designed to bring all reductions back to zero as close as possible.

If there are reductions, the reductions happen from the bottom up.  Say there are 20 positions, and we are reducing that by 1, then the bottom person gets reduced, not the top person.

So, if someone is in their current position, and their first choice is to remain in that position, they get to stay unless they are so junior that they cannot stay.  I think right now it is doing the reductions from the top down, but it needs to allocate to each job until it gets to the desired number for that job, then begin the reductions.

I think a perfect example is sen number 76.  He should have stayed in his position.  The reductions aren't being applied from the bottom.  We would have a riot if someone who were number 76 out of 1700 couldn't keep his position!  :)  Number 169 is another good example.

I get the sense that this is very close.  We have a manual run of this completed now, so I can specifically go against that and compare.  There are still big problems, but I think they are all related to displacements being handed down in the wrong order.  Again, it would be a big help if you could post the results in each category alone, so I can test.  

Also, the current Analysis table looks like this:

      Current TOTAL      Current ON-LINE      Current OFF-LINE      DESIRED ON-LINE      Future TOTAL      Future ON-LINE      Future OFF-LINE      TOTAL REDUCTION            NEW TOTAL
M2 J1 C1      299      237      62      211      0      0      0      -26            273
M1 J1 C1      500      433      67      433      0      0      0      0            480
M1 J1 C2      72      67      5      67      0      0      0      0            67
M2 J2 C1      265      230      35      204      0      0      0      -26            250
M1 J2 C1      474      430      44      430      0      0      0      0            440
M1 J2 C2      66      62      4      62      0      0      0      0            67
M2 J1 C3      0      0      0      56      0      0      0      56            67
M2 J2 C3      0      0      0      56      0      0      0      56            56
NEW HIRE      24      24      0      0      0      0      0      0            0
TOTAL      1700      1483      217      1519      0      0      0                  1700
Avatar of graysoc

ASKER

I'll be available tonight to work on it, if you're around.

Thanks!
OK, so I'm going back to the code here: ID:35089546 because that seemed to work more towards what you need.

Can you run that one again and see what the issues are?

For me to write the data to each of the route sheets, I'll need to understand what they're for.  I'm currently confused by the fact that they all seem to have a mismatch of data on them.  If they are to be populated with only each new bid that was successfull, then should they be placed on that sheet for their winning bid (which the value I currently write to column D on ALL BIDS)?

If that is the case, would it also be helpful if each sheet was cleared before it processed the bids?

Regards,

Rob.
Avatar of graysoc

ASKER

Sorry for the delay...

Can you run that one again and see what the issues are?

The issue is that someone who's current position is the same as their first bid preference shouldn't be removed from that position unless there is a reduction AND they are one of the bottom XX number of people that cannot continue to be there.  For instance, number 184.  He wants to remain in his current position, and he is plenty senior enough to hold it.  He should stay there.  The only people who should be removed are the bottom 25 people, or if 25 of the more senior guys bid off of it, like number 185 did, then no one gets displaced off.  If 26 people bid off of it, then 1 person who wanted that position, but who's current position is somewhere else, can bid into that resultant vacancy.  

Does that help?  That necessitates multiple runs because the first run should see who bid off of a particular seat, then a calculation should be done to see if the reduction is still the same.  From there a bid should be run to fill the resultant vacancies, and so on.

To explain a little more:  If there are -25 positions, and number 184 bids off, then now there are -24 positions, etc.  When it is done, the number of positions should equal the new total in column L.

From there you move on to the next batch, and so on.  I'm thinking it will take about 3 runs per position to be accurate.


For me to write the data to each of the route sheets, I'll need to understand what they're for.  I'm currently confused by the fact that they all seem to have a mismatch of data on them.  If they are to be populated with only each new bid that was successfull, then should they be placed on that sheet for their winning bid (which the value I currently write to column D on ALL BIDS)?

For this part, the current position and all of the bids are on there just to show what they bid for verification purposes.  I can at a glance look at the list, and see errors.

And yes, only the positions that were successful for that sheet should be posted.  So there should be 282 people on the M2 J1 C3 sheet, because that is the number in column L (New Total).
Avatar of graysoc

ASKER

And thanks again!!
Hi, sorry for my delay.  I'll have to digest your information a bit and work out a method to accomodate it.  I'll try to return to it tomorrow.  Been quite busy over the last few days.

Rob.
Avatar of graysoc

ASKER

No problem.  Take your time.  I appreciate the help, and time isn't an issue at this point.
Avatar of graysoc

ASKER

Any updates?
Sorry mate...I know it's been a while....I just need to find an hour or so to sit down and rethink this one.  I'm winding down at work a bit, so I will have some time this week to check it out for you.  Sorry it's taking a while...

Rob.
OK, I'm going to start working on this one again.  I'm thinking of processing like this:

1) Read the bids for bid 1, storing a hypothetical result set in memory, just to see what the reductions are if everyone got there first bid.
2) Based on that new set, process the bids for bid 1 from the bottom up, and determine whether each person can have that, and allocate the first bid or not.
3) Repeat the above two steps for bid 2, 3, 4, and so on.

My main concern, which is probably why I've been so confused, is that during the bid 2 cycle, a spot may become available that wasn't allowed to be allocated during the bid 1 cycle.  I have no idea how we would handle that situation, but I'm hoping you're about to tell me that it doesn't need to work that way.  That is, once all bid 1's have been processed, that's it, regardless of the result of the successive bids.

Let me know your thoughts on that, and I'll get started.

Again, sorry for my delay.

Regards,

Rob.
Avatar of graysoc

ASKER

No worries.  There is no hurry.  

Unfortunately, the fact that a "resultant vacancy" exists makes this automation very difficult.  Essentially, if someone bids OFF of a job and into another one, that can create a vacancy that another person can fill.

I'm concerned abotu step 2.  Why does it fill from the bottom up?  All bids should always be filled from the top down.  No one should ever get a position if they are junior to another person that wanted that position, and didn't get it.

If you go with what you are thinking, I can test, and see how it works.  I'm just confused by the running from the bottom up concept.
Well, my thinking is that with the bids hypothetically allocated based on first bids, ignoring any reduction counts, then you would see how many could not be allocated the position if the reduction is negative.

For example, if M2 J1 C1 has a current reduction of -26, this means that 26 people need to be de-allocated from it.  Now, assuming the hypothetical allocation of all first bids resulted in 20 people leaving the position, and 5 people bidding into the position, then the hypothetical reduction is -11.
Then, we go from the bottom up, checking who is *currently* in that position, and moving them out, by either:
1) if their first bid *is* M2 J1 C1, then don't let them have it, and wait until bid 2
2) if their first bid *is not* M2 J1 C1, *and* the target position has a positive reduction, allow that bid
3) if their first bid *is not* M2 J1 C1, *and* the target position has a negative reduction, wait until bid 2
4) for those further up the list, if the reduction has become zero, and bid 1 is the current position, they stay there

This way, after bid 1, none of the reductions will be any further negative, moving out the people from the bottom up, so that by the time it gets closer to the top, the reduction  becomes zero, and people who bid to stay there will stay.

I think it makes sense, because you wanted the top most people to keep their positions, so this is what I have come up with.  While it sounds like it might work in theory, I'll have to wait until I put it into practice to see what happens.

So, another think for me to clarify...the individual sheets....do you want those wiped at the start of each new run, and then filled with those rows that have successfully bid into that position?

Rob.
Avatar of graysoc

ASKER

I apologize, I didn't see this response!

That sounds great.  I'll play with it if you get another code running, and get back to you.

As far as the sheets go, yes, if they can clear out and repopulate that is fine.  

Let me know if you have other questions.

Thanks!
Hi, I'm working on it.  It's a large data set, so I'm having some difficultly in deciding whether it's working the way I expect....I'll do some more testing....

Rob.
Avatar of graysoc

ASKER

Thanks.  If it helps, I can give you the results from our manual bid so you can see if it comes out the same.
Hi, I really don't know if I've gone about this the right way at all....it appears like it might be OK, but it's hard for me to analyse.

Here's the file I was working with.  You can run the macro to see how it goes.  It populates the tabs now as well, hopefully correctly, but it basically puts the successful bid into the appropriate sheet.

Regards,

Rob.

Scrubbed-Version-V2.xlsm
Avatar of graysoc

ASKER

I'm still looking it over.  I'll be back with you soon.
Sure, no worries.

Rob.
Avatar of graysoc

ASKER

Hi Rob,

I believe we need to talk on the phone, if at all possible.  

The way it is running now the worksheet 'ALL BIDS" is changing after the process. That page should remain the same because the bids are user defined.  The employees set that, and from that page we determine where to put everyone.

At a first glance, it isn't processing properly.  It is hard to see what is going on.  Can we start out by just making the change that the All Bids sheet stays the same?  Maybe I should put some fake names in there under the name column?  

Once the awards are processed, they need to be ascending seniority order, so I can determine if they were assigned correctly.  If you look at one of the first iterations of this, it was working properly, in that all of the bids were processed in the appropriate order, etc.  The thing that was broken was that it was awarding positions even if there was a reduction in that category.  It just needs to look, and if there's a reduction, no one gets assigned that position.  I think it needs to go like this.

Step 1.  Determine from the analysis page (current reduction column) which categories get increases or decreases.  Take note of that maximum number in each category.  (If negative, someone is going to be displaced.)

Step 2.  Process the bids, counting as it goes to stop placing in each category that has a reduction once the total number determined before is reached.  Once it reaches that number, if someone attempts to bid into that position, do not allow it.

Step 3.  Count the people in each category after the first bid, and compare with the total reductions column again.  If the number of people in a category has changed (and is thus no longer negative due to someone bidding OUT of that position), then that new total number is attempted to be reached in the second processing.

Step 4.  Repeat step 2, then step 3 until all bids are processed at least once.

It should be bid from the top-down, I believe.

Again, the goal is to reach the number in the desired on-line column.  If the current on-line column is greater than the desired on-line column, that will require a reduction in that category.

 I'm still having a hard time wrapping my head around the fact that it is processing from the bottom-up.  In a seniority system, number 1 gets first choice so if you process starting at the bottom, that can't happen.

Does that make more sense?  Let me know what part is confusing, and I'll explain more. :)

Thanks!

Hi, talking on the phone would be difficult....maybe on Wednesday I could use Skype, but what is our timezone difference?  Right now it's 4:00pm on Friday afternoon.

I think I'm going to change my angle to look at the Current Total column, and Desired On Line columns only.  Then after each processing, recount the total for each category and work towards Desired On Line as the maximum.

One of the main reasons I wanted to work from bottom up is this:
If you were processing from top down, say person 1 wanted to stay in the position, but the reduction is -1. This means that person (at highest seniority) will not stay there (ie have that bid processed).  But then person 2 bids out of that same position, so it's become available.  We would have no way of "reprocessing" person 1's bid.

Especially since you want the higher seniorities to have preference, this is why I was working the other way.  I'll have to rethink it again.....

Regards,

Rob.
Avatar of graysoc

ASKER

I see where you're going.  I'm willing to get up at any time to talk on Skype.  Just let me know.  Anything we discover or decide off-line, we will have to post here, obviously.  

I just don't think that we can process from the bottom-up, since that will result in people on the bottom getting something posted to them out of order.  Possibly we can run the first choices twice?  

G
Apologies for my delay.

I'm tied up for most of the week.....I will be a bit busy.

I have simplified the code quite a bit this time round.  It just felt too complicated the way we were trying to work it.

I've been thinking....why do we even need to take the current placement into account at all?  Why do we need to work around the reduction value?  What I have done here is read the maximum for each route, start "current" placements all at zero, and then allocate each bid (from top down you'll be happy to know ;-)) in turn until the maximum (desired online) for that route is reached.  This seems logical, because even if they wanted to stay in the current position, this would be reflected in their first bid, so they would get it, based on seniority, and positions available.

Each bid is also recorded on each sheet, and sorted at the end.  I have also stopped the changing of any data on the ALL BIDS sheet, except that I am flagging a bid as successful via a value in column T.  This value, for the moment, is left in, so that you run can run down that column and easily see who hasn't been allocated.

What do you want to do with such people?  From my testing, the maximum for all of their bids was reached before they could be allocated one, so they are left nowhere?

When the code has finished running, you will see the current allocations, along with the corresponding max, and you will see they are all full.

Regards,

Rob.
Sub FillBids()

Dim dctRouteMax As Dictionary
Set dctRouteMax = New Dictionary
Dim dctRouteCurrent As Dictionary
Set dctRouteCurrent = New Dictionary

Application.ScreenUpdating = False

numBidRows = Sheets("ALL BIDS").Range("A10000").End(xlUp).Row

strOldOnline = "Old online values were:"
For Each c In [myRoutes]
   dctRouteMax.Add c.Value, c.Offset(0, 4).Value ' this is where I get the desired on line value
   dctRouteCurrent.Add c.Value, 0
   strOldOnline = strOldOnline & vbCrLf & c.Value & ": " & c.Offset(0, 2).Value
   Sheets(c.Value).Rows("2:10000").Clear
Next c

Sheets("ALL BIDS").Range("T2:T10000").ClearContents

blnNotAllAllocated = False
For k = 1 To 14 'num of bids per person
    For j = 2 To numBidRows
        thisRoute = Sheets("ALL BIDS").Cells(j, k + 4).Value
        Application.StatusBar = "Processing bid " & k & ". Processing seniority position " & Sheets("ALL BIDS").Cells(j, 1).Value & " ..."
        If Sheets("ALL BIDS").Cells(j, "T").Value = "" Then
            If thisRoute <> "NULL" Then
                If dctRouteCurrent(thisRoute) < dctRouteMax(thisRoute) Then
                    dctRouteCurrent(thisRoute) = dctRouteCurrent(thisRoute) + 1
                    Sheets("ALL BIDS").Cells(j, "T").Value = Sheets("ALL BIDS").Cells(1, k + 4).Value & ": " & thisRoute
                    If thisRoute <> "NEW HIRE" Then
                        Sheets(thisRoute).Rows("2:2").Insert Shift:=xlDown
                        Sheets(thisRoute).Rows("2:2").Clear
                        Sheets("ALL BIDS").Range("A" & j & ":S" & j).Copy Sheets(thisRoute).Range("B2")
                    End If
                End If
            Else
                'Sheets(thisRoute).Range(Cells(j, 1), Cells(j, 15)).Interior.ColorIndex = 3 ' this would do the whole row
                'Sheets("ALL BIDS").Cells(j, 4).Interior.ColorIndex = 3   ' this does just the name column
            End If
        End If
    Next j
Next k

strNewOnline = "New online values are:"
For Each strR In dctRouteCurrent
    strNewOnline = strNewOnline & vbCrLf & strR & ": " & dctRouteCurrent(strR) & vbTab & "Max: " & dctRouteMax(strR)
    If strR <> "NEW HIRE" Then
        intRouteLastRow = Sheets(strR).Cells(65536, "B").End(xlUp).Row
        Sheets(strR).Sort.SortFields.Clear
        Sheets(strR).Sort.SortFields.Add Key:=Range("B2:B" & intRouteLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With Sheets(strR).Sort
            .SetRange Sheets(strR).Range("B1:T" & intRouteLastRow)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Sheets(strR).Activate
        Sheets(strR).Range("A1").Select
    End If
Next
Sheets("ALL BIDS").Activate

'Sheets("ALL BIDS").Range("T2:T10000").ClearContents

MsgBox strNewOnline & vbCrLf & vbCrLf & strOldOnline

Application.StatusBar = False
Application.ScreenUpdating = True

End Sub


Sub ClearTabs()

Sheets("All BIDS").Cells.Interior.ColorIndex = 0
 
For Each c In [myRoutes]
  Sheets(c.Value).Range("A2:VV1000").ClearContents
Next c

End Sub

Open in new window

Avatar of graysoc

ASKER

I agree with you, and wish it were that simple.

Unfortunately, just because someone's first choice is X, doesn't mean they get it.  If there is a reduction, people come out of that position, but no one goes into it, even if it is their first choice, and they are number one on the list.

If there is an increase, then yes, people can bid over to that position.  If enough people bid out of a position that has a reduction, then there can be "resultant vacancies."  At that point, people can bid into that position, but not before.

It may just be impossible to make this work in an automated way.

Right now, we manually process each bid, keeping track of those who leave a position.  It takes several iterations to get it all right, and generally there are a few errors that are pointed out after the fact.

To answer your question about where people go who can't hold any position, we would manipulate the numbers to be sure that all are accounted for, and place the extras somewhere we think we may need them.
Avatar of graysoc

ASKER

Take your time, I know you're busy.  And again, it may just not be possible to have work properly.

Thanks!
Avatar of graysoc

ASKER

Hi there. Any updates to this question sir?
My apologies....wow, where did a month go!?!?!

@ Teylyn, I still intend to give this another go or two, so let's leave it ioen for now.

@ graysoc, this sort of automated "intelligence" may be out of my league in the end, or just not possible without manual intervention.  I will try again at some stage this week.

Regards,

Rob.
OK, before I work this over again, I need to try and clarify what you have said.

You have said:
" If enough people bid out of a position that has a reduction, then there can be "resultant vacancies."  At that point, people can bid into that position, but not before."

The only way that I can think of to get this to work is to re-process the entire lot every time a change is made (that is, every time someone successfully bids into a new position).

This will take far longer, and I'm not even sure if it will work, but I think it would go like this:
 - All first bids are unable to be allocated until say pos 500, where their first choice *is* available. So they move.  This makes a resultant vacancy in the position they have left.
 - So we start again, and find that pos 200 wanted to go where 500 left, so they go there, and we start again

I guess the thing that confuses me is this: Do you ever *change* someone's successful bid allocation, it a resultant vacancy appears in an earlier bid of theirs?  I certainly hope not, otherwise I'd be stumped on how to account for that.  I would hope that once someone has been allocated a bid, we flag them as not being able to move, and hopefully we have everyone's bids done by the end of it all.

Regards,

Rob.
Avatar of graysoc

ASKER

Nope.  Once someone gets a choice, they have it.  If you're tired of working on this, I understand!

I'm willing to let the thing run for hours if it saves me from having to do this manually.  It takes about 2 days worth of work to get this done, and then there are inevitably errors everywhere.  

I'm available by Skype any time to discuss the details, and then post the code we come up with on here for the site.
I just ran the updated code, and it took three hours on my machine, but I realised I was missing something, so I've made another change, and will wait until that's finished.

Rob.
This took 5 hours.  The reason is, because we start again with every change that is made, it starts again as many times as there as rows.

I've attached my processed workbook for you to look at to save you from waiting 5 hours for the results.

Regards,

Rob.
Sub FillBids()

Dim dctRouteMax As Dictionary
Set dctRouteMax = New Dictionary
Dim dctRouteCurrent As Dictionary
Set dctRouteCurrent = New Dictionary

Application.ScreenUpdating = False

numBidRows = Sheets("ALL BIDS").Range("A10000").End(xlUp).Row

strOldOnline = "Old online values were:"
For Each c In [myRoutes]
   dctRouteMax.Add c.Value, c.Offset(0, 4).Value ' this is where I get the desired on line value
   dctRouteCurrent.Add c.Value, c.Offset(0, 2).Value ' this is where I get the current on line value
   strOldOnline = strOldOnline & vbCrLf & c.Value & ": " & c.Offset(0, 2).Value
   Sheets(c.Value).Rows("2:10000").Clear
Next c

Sheets("ALL BIDS").Range("T2:T10000").ClearContents

j = 2
For k = 1 To 14 'num of bids per person
    For j = 2 To numBidRows
        thisRoute = Sheets("ALL BIDS").Cells(j, k + 4).Value
        currentPosition = Sheets("ALL BIDS").Cells(j, "C").Value
        Application.StatusBar = "Processing bid " & k & ". Processing seniority position " & Sheets("ALL BIDS").Cells(j, 1).Value & " ..."
        If Sheets("ALL BIDS").Cells(j, "T").Value = "" Then
            If thisRoute <> "NULL" Then
                If thisRoute <> currentPosition Then
                    If dctRouteCurrent(thisRoute) < dctRouteMax(thisRoute) Then
                        dctRouteCurrent(thisRoute) = dctRouteCurrent(thisRoute) + 1
                        dctRouteCurrent(currentPosition) = dctRouteCurrent(currentPosition) - 1
                        Sheets("ALL BIDS").Cells(j, "T").Value = Sheets("ALL BIDS").Cells(1, k + 4).Value & ": " & thisRoute
                        If thisRoute <> "NEW HIRE" Then
                            Sheets(thisRoute).Rows("2:2").Insert Shift:=xlDown
                            Sheets(thisRoute).Rows("2:2").Clear
                            Sheets("ALL BIDS").Range("A" & j & ":S" & j).Copy Sheets(thisRoute).Range("B2")
                        End If
                        j = 2
                        k = 1
                        Exit For
                    End If
                Else
                    If dctRouteCurrent(thisRoute) <= dctRouteMax(thisRoute) Then
                        Sheets("ALL BIDS").Cells(j, "T").Value = Sheets("ALL BIDS").Cells(1, k + 4).Value & ": " & thisRoute
                        If thisRoute <> "NEW HIRE" Then
                            Sheets(thisRoute).Rows("2:2").Insert Shift:=xlDown
                            Sheets(thisRoute).Rows("2:2").Clear
                            Sheets("ALL BIDS").Range("A" & j & ":S" & j).Copy Sheets(thisRoute).Range("B2")
                        End If
                        j = 2
                        k = 1
                        Exit For
                    End If
                End If
            Else
                'Sheets(thisRoute).Range(Cells(j, 1), Cells(j, 15)).Interior.ColorIndex = 3 ' this would do the whole row
                'Sheets("ALL BIDS").Cells(j, 4).Interior.ColorIndex = 3   ' this does just the name column
            End If
        End If
    Next j
Next k

strNewOnline = "New online values are:"
For Each strR In dctRouteCurrent
    strNewOnline = strNewOnline & vbCrLf & strR & ": " & dctRouteCurrent(strR) & vbTab & "Max: " & dctRouteMax(strR)
    If strR <> "NEW HIRE" Then
        intRouteLastRow = Sheets(strR).Cells(65536, "B").End(xlUp).Row
        Sheets(strR).Sort.SortFields.Clear
        Sheets(strR).Sort.SortFields.Add Key:=Range("B2:B" & intRouteLastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With Sheets(strR).Sort
            .SetRange Sheets(strR).Range("B1:T" & intRouteLastRow)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Sheets(strR).Activate
        Sheets(strR).Range("A1").Select
    End If
Next
Sheets("ALL BIDS").Activate

'Sheets("ALL BIDS").Range("T2:T10000").ClearContents

MsgBox strNewOnline & vbCrLf & vbCrLf & strOldOnline

Application.StatusBar = False
Application.ScreenUpdating = True

End Sub


Sub ClearTabs()

Sheets("All BIDS").Cells.Interior.ColorIndex = 0
 
For Each c In [myRoutes]
  Sheets(c.Value).Range("A2:VV1000").ClearContents
Next c

End Sub

Open in new window

Scrubbed-Version-V4.xlsm
ASKER CERTIFIED SOLUTION
Avatar of graysoc
graysoc
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of graysoc

ASKER

Thanks for the help.  I'm going to give up on this for a while.  The processing is working, but not in the right order.  It isn't worth the effort that you're having to put in.

Thanks,