How to find the lowest value number in a gap from a sequence of numbers?

JJackson
JJackson ✭✭
in Formulas and Functions

Hello!

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.

example.png

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!!

Tags:
· Share on FacebookShare on Twitter

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭

    @JJackson

    How many total tools do you have? One way I would probably do it (depending on total # of tools and how often I add new tools) would be to create a database sheet of my tools. Possibly the tool #, description, and an Assigned To column on the database sheet. Then in the database sheet of tools you can create an Index Match formula in it's Assigned To column to map to this other sheet where the person is assigned a tool. That way when you browse your tools database sheet it's easy to see who's assigned to what and which tools are not assigned to anyone.

    The only real issue would be scalability. If you have a TON of tools it could take a bit of legwork to get it set up. However, after setup you'd just add a new tool to the tool database sheet when new tools are added. On the other sheet you just enter tool #'s to a person when they're assigned. Not too much manual manipulation needed.

    Let me know if you need example sheets of this method working.

    · Share on FacebookShare on Twitter
  • JJackson
    JJackson ✭✭
    https://community.smartsheet.com/discussion/comment/330130#Comment_330130

    At the moment we have about 300-350 tools assigned and are continuing to assign as we grow. We are trying to ensure that returned tools are reassigned before creating new tools as well.

    So, say tool #213 was returned to us and we needed to assign it to someone new, instead of creating tool #351 we would want to assign the newly returned #213 to this new person. Right now we sort the numbers in order and search for the tool we have in our hands to reassign. I would like to be able to identify when there is a gap in a sequence of numbers and ideally have the numbers in the gap listed somewhere so we know to assign the lowest value number tool before creating completely new tools in the system.

    I would love an example if you are able! I may have to come at the situation in a different way, I just have a lot of tools with already assigned numbers and we do get a few returned to us (The frequency of which is hard to predict) so I don't know if I would want to auto generate a number for the tool. Auto generation would be useful for new tools but I have so many existing that the existing tools would get scrapped.

    Thank you!

    · Share on FacebookShare on Twitter
  • Mike TV
    Mike TV ✭✭✭✭✭✭

    @JJackson

    What format do you use for your tool numbers? Also, are we talking about different types of tools such as you have 100 hammers, 100 wrenches, 100 screwdrivers, and 50 saws? Do different tool types have different tool number formats such as #HAM-001, #WRC-001, #SCR-001?

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!