How to Automate a Rostering System

[MarTyr109]

Hello, and thank you to all who respond constructively.

I am currently attempting at automating a rostering system for 50 employees. They are unionized, and they have the ability to bid on different assignments.

What I am trying to figure out how to do is to have a cell in a column be able to show a value as long as the value is not in the column above it AND to scan the row in which it resides. For example:

Name	Bid 1	Bid 2	Bid 3	Awarded Bid
Gregg	722	711	700	722
Sam	722	711	700	711
Dave	722	711	700	700

Though it is quite simple to deal with a small/short example manually in the one above, I am in a position in which all of the employees (50) have more (10) bids. Cross referencing these numbers is more cumbersome.

Now when an employee goes on vacation, I would like to change one value and have the rest of the sheet update. Like the following:

Name	Bid 1	Bid 2	Bid 3	Awarded Bid
Gregg	Vay	Cay	Shun!	N/A
Sam	722	711	700	722
Dave	722	711	700	711

I understand that the easier method would be to write a script with vba, but the company I work for uses Teams for their Excel tools which would stop the script from working.

Thank you, again, for any who have a code or two that would help me out.

[#2]

@MarTyr109 

=IF(AND(COUNTIF($B$1:B1,B2)=0,COUNTIF(B2:D2,B2)=0),B2,"N/A")

This formula checks if the value in cell B2 is unique in the range B1:B2 and in the range B2:D2. If the value is unique in both ranges, the formula returns the value in cell B2. Otherwise, it returns “N/A”.

You can use this formula to check if an employee’s bid is unique among all bids in the same row and among all awarded bids in the column above. If the bid is unique, it can be awarded to the employee.

However, this formula only checks one bid for one employee. To automate the process of awarding bids for all employees, you may need to use more advanced techniques such as Office Scripts or VBA.

[#3]

@MarTyr109 ,

About the automation, as mentioned by @NikolinoDE , you may use VBA or Office Scripts, and both tools are connected to another Microsoft solution, which is the Power Automate, which contains several connectors with other Microsoft products and third part ones. As in Excel, the Power Automate community is very active and will be able to help you with the automation share of your solution.

Here are some links about it:

Blog post that compares VBA with Office Scripts: http://digitalmill.net/2023/06/10/office-scripts-the-new-vba/

Power Automate documentation: https://learn.microsoft.com/en-us/power-automate/ 

Access to Power Automate Community: https://powerusers.microsoft.com/t5/Forums/ct-p/FL_Comm_Forums

[#4]

have the rest of the sheet update

Sorry,I am confused about what you meant rest of the sheet?