How to find the lowest value number in a gap from a sequence of numbers?
I am currently working on a sheet that will allow us to assign a numbered item to a teammate and keep track of who has what numbered item for traceability purposes. The audit process has been completed and fully automated but I wish to make this more automated during the assignment process.
Currently, we manually search for missing tool numbers, or if we have the returned tool in our hands we re-submit the tool # and manually delete the original row.
I want to have a system built in where when we get a tool returned to us and we remove it from the smartsheet, the system will notice a gap in a sequence of numbers and (Possibly in a different column) return the lowest value within the gap in sequence.
In the example below, I have 8 employees who have assigned tools with identifiable numbers assigned to each tool. The tool numbers are in a range of 1-10, two of the tools have been returned to us and we wish to reassign them. I want to build something that would, in the following column, identify that tool # 6 and tool #8 are the numbers missing in the sequence. I could build an automation from there I believe.
This may be pretty complicated (and our range is much larger than 10), I have tried a lot of different avenues to get the returns I would like but I have had trouble getting any of these avenues to work.
Any help on this issue would be very much appreciated, I've exhausted my teammate who is also good with Smartsheet and we both have not been able to make this idea work.
Thank you in advance!!