Search code examples
excelnumbers

Find next smallest number

I have equipment list:

    A   B
1   1   Motor
2   2   Flange
3   3   Pipe
4   4   Pump
5   5   Bolt
6   6   Nut
7   7   Washer

The list names are coming from another function and if some of the equipment is not listed I need to reorganize numbering automatically with excel function (no macro). For example customer needs only Motor, Pipe and Bolt. Then the list should look like:

         A           B
1        1           Motor
2        (empty)     (empty)
3        2           Pipe
4        (empty)     (empty)
5        3           Bolt
6        (empty)     (empty)
7        (empty)     (empty)

Values in cells B are already working. So if value in cell B2 is (empty) “” in corresponding A2 I need it to be empty too. But if in cell B2 is word motor or any number I need it to pick the next available number from the top. Also numbering can be started from cell A5, depending on the list of equipment. I have tried to use IF function but was not able to get the working one. Hopefully I have described it clearly. Any help?

Solution

  • This worked for me. The formula in A1 is

    =IF(B1="","",COUNTIFS(B1:$B$1,"<>"&""))

    You are basically counting non-empty cells in column B up to the current row and only showing the count in rows where the non-empty cells are. Hope it helps.