# How to Automate a Rostering System

Copper Contributor

# How to Automate a Rostering System

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.

3 Replies

# Re: How to Automate a Rostering System

=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.

# Re: How to Automate a Rostering System

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.

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/