Return next unique number in a range


New Member
Jul 28, 2020
Office Version
  1. 365
  1. Windows
Good Morning!

I have created this account specifically to ask this question, so I apologise in advance if I break any rules, guidelines or forum etiquette about posting.

I am looking for help with a spreadsheet that details all of the hire equipment in our company, detailed across multiple sheets within a workbook which are broken down by various product groups. There are 80 sheets in total.

On each sheet in column A there are a list of item numbers for that product group and each item has a unique ID where the first (usually)4 digits are letters, and the next 4 are numeric. Over the years of trading, some numbers have been scrapped and also deleted entirely from our system, meaning the number is essentially vacant.

I'm looking for a formula that will not only identify the next available blank cell in a column, but will also determine what the next number will be.

I've attached a screenshot of an example spreadsheet, where I would want the formula to go in the yellow highlighted cell (B6), and in this instance, return a value of LGAH0005, as this is the next available unique number in the list of A9-A18.

I'm absolutely open to ideas on how to do this a better way, but this is the best way I can think of to establish what your next unique available number is within a sequence at a glance.

Thank you very much in advance, this has been melting my mind so any help will be greatly received!




  • for mrexcel 2807.png
    for mrexcel 2807.png
    12.8 KB · Views: 14


Well-known Member
Dec 30, 2008
Office Version
  1. 365
  1. Windows
For an example of how Jason's formula has been affected by this, I've just created LGBL product group sheet, and there are only two items on there LGBL0001 & LGBL0002.
So, in the 'Next' field, this should ideally be showing me LGBL0003 but I get the #N/A using Jason's formula, and I am sure it is because the 'current available' is 0, owing to the fact that both the items are currently showing 'in service' in column F. 'Current Available' is currently running on a COUNTIF function that I've added to return a value where 'Available' is showing.
I did think that it should be the case but forgot to edit the formula for 'available'. Peter's formula for that is the exact one that I was going to use and will prevent the #N/A errors.

Main thing is, you have a method that works :)

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Watch MrExcel Video

Forum statistics

Latest member