Return next unique number in a range

JimmyLGS

New Member
Joined
Jul 28, 2020
Messages
4
Office Version
  1. 365
Platform
  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!

Best

Jimmy
 

Attachments

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,602
Office Version
  1. 365
Platform
  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 :)
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Watch MrExcel Video

Forum statistics

Threads
1,129,438
Messages
5,636,292
Members
416,911
Latest member
jafornwalt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top