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: 14

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Giordano Bruno

Well-known Member
Joined
Jan 7, 2007
Messages
1,231
Hi Jimmy and welcome. May I suggest the following array formula?
=MIN(IF(COUNTIF(A9:A2000,"LGAH*"&ROW(A9:A2000)-ROW(A9)+1)=0,ROW(A9:A2000)-ROW(A9)+1))
Entered of course with Ctrl-Shift-Enter. (The number 2000 is arbitrary and may be extended or reduced)
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,986
Office Version
  1. 2019
Platform
  1. Windows
Welcome to MrExcel:)

Certainly nothing broken that I can see (unless you have cross posted without informing us.

The only thing that I would suggest would be the use of XL2BB for posting examples instead of screen captures (the button on the toolbar will take you to the guide page).

the first (usually)4 digits are letters, and the next 4 are numeric.
Would all of the ID's on any one sheet have the same format and the same prefix? e.g. using your screen cap example, could there be an ID on that sheet that doesn't start with LGAH or one that has more or less than 5 numbers?

Here's a quick idea based on the example that you gave us, but it may not work with all scenarios. The prefix in B3 could be extracted from A9 using a formula if needed but I've omitted that for now.

Book1
AB
3PrefixLGAH
4Count9
5Available1
6NextLGAH0005
7
8Item No.
9LGAH0001
10LGAH0002
11LGAH0003
12LGAH0004
13
14LGAH0006
15LGAH0007
16LGAH0008
17LGAH0009
18LGAH0010
Sheet6
Cell Formulas
RangeFormula
B4B4=COUNTA(A9:INDEX(A:A,MATCH("zzz",A:A)))
B5B5=COUNTBLANK(A9:INDEX(A:A,MATCH("zzz",A:A)))
B6B6=B3&TEXT(SUBSTITUTE(INDEX(A:A,MATCH(TRUE,ISBLANK(A9:INDEX(A:A,B4+B5+8)),0)+8-1),B3,"")+1,"0000")
Press CTRL+SHIFT+ENTER to enter array formulas.
 

JimmyLGS

New Member
Joined
Jul 28, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Giordano, Jason....thank you both for your input!

Giordano,
I tried your formula and it brought me back the numeric, without the '00' prefix, so in my example it returned '66' rather than LGAH0066. I concede this may be to do with something I did when copying the formula across to the spreadsheet, but thank you very much for your help!

Jason,
I've been a member of a few different boards and the same usually applies in that the rules are there and to be adhered to without exception (rightly so). I suppose my intention with the disclaimer was to let anyone know that if I had offended or breached a posting rule it was without malice.

And definitely no cross posting, this was my first post after creating an account. I have to say that with the very helpful responses I've had, it may not be the last!

Duly noted about XL2BB. I've tried to download it, but already struggling to find it in the add ons as it recommends in the link(?)

Concerning the ID's it absolutely is the intention that the first two letters will always be LG. In the majority of cases, the next 2 would also be letters, but we do have a very small percentage where it would only be 1 letter rather than 2. Example, LGS0001, LGS0002 or LGT0001, LGT0002 etc etc
These are rare though. The vast majority is 4 letters, 4 numbers. Perhaps in these instances, the formula for the prefix extraction you make mention of would be best.
We may also have a situation whereby the product group (LGAH for example) may contain some numbers that start LGOB, because the item may have been created as an 'odd ball' BEFORE the product group was created, if that makes sense? But in these instances, we would still want the next unique number in that product group range to be LGAH, so I assume that the newly added prefix field sorts that issue out(?)
If I can resolve the XL2BB issue, I can share a full list of the numbers and their product groups, which will give you the full idea of the structure, and which product groups will cause me problems, and how to work around them.

The black and white of the formula you have provided me is perfect, thank you! This worked when tested in a few different product groups. I've deleted some numbers in the range to test if it changes the value the way I want and it does, thank you very much for that...:love:

I will be interested in your thoughts of a workaround for those with exceptions.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,986
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Jimmy,

I'll have a look at the exceptions for you a bit later on, day job is about to get in the way of forum time :( Looking at your example of LGS0001, etc. Would it be fair to assume always 4 numbers?

The formula should ignore the 'odd ball' numbers anyway as they don't match the expected prefix.

Looking at your example of LGS0001, etc. Would it be fair to assume always 4 numbers?

Regarding XL2BB, the add-in is not installed by default, there is an orange button the the right of the guide page (link below) where you can download it in the form of a zip file.
Once downloaded you will need to extract it to a folder where it can reside happily without being moved before adding it to excel as per the guide instructions. (Another member tried to move the extracted file after adding it to excel and had a lot of trouble getting it to work afterwards).
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,826
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

Here is another approach to consider. I have used a couple more helper cells but it should work for any length prefix or numerical part (within reason ;))
I have assumed data will not go beyond row 1000 but you can easily change that in the B4 formula if required.
Even if there are no blanks in the data below row 8, this will still report 1 available, my reasoning being that in such circumstance the 'next' will be whatever comes after the last entry in the list.
This will fail if cell A9 is deleted but hopefully the user would easily see that is the first available.

One further assumption is that you have the SEQUENCE function in your version of Excel 365. If not an adjustment can be made.

JimmyLGS 2020-07-28 1.xlsm
AB
1
2Count9
3Available1
4PreviousLG0004
5PrefixLG
6NextLG0005
7
8Item No.
9LG0001
10LG0002
11LG0003
12LG0004
13
14LG0006
15LG0007
16LG0008
17LG0009
18LG0010
19
Sheet1
Cell Formulas
RangeFormula
B2B2=COUNTA(A:A)-COUNTA(A1:A8)
B3B3=MAX(COUNTBLANK(A9:INDEX(A:A,MATCH("zzz",A:A))),1)
B4B4=INDEX(A9:A1000,MATCH(TRUE,A9:A1000="",0)-1)
B5B5=LEFT(B4,AGGREGATE(15,6,FIND(SEQUENCE(10,,0),B4),1)-1)
B6B6=B5&TEXT(SUBSTITUTE(B4,B5,"")+1,REPT(0,LEN(B4)-LEN(B5)))



I think that we have all assumed the Item numbers will be kept neatly in order other than any deletions. That is, there is no check in mine for example that LG0005 does not already exist somewhere else in the range.
 

Giordano Bruno

Well-known Member
Joined
Jan 7, 2007
Messages
1,231

ADVERTISEMENT

Hi Jimmy,
You asked for the missing number, so I gave you a number. You did nothing wrong entering the formula. If you want to create the missing characters, you could add the following. I'm assuming the number formula is in cell A1
="LGAH"&REPT(0,3-INT(LOG10(A1)))&A1
Entered normally
 

JimmyLGS

New Member
Joined
Jul 28, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi Jason

I empathise, the job is swamping me now this afternoon too in the run down the home straight to 5!

That is a fair assumption, whether the format is either 3 or 4 letters, the numeric would still be 4 digits, in the vast majority of cases.

In our existence as a company, we've made the grave mistake of allowing various people over the years to handle the fleeting of new assets, and it has cost us with some of the codings.
There may be a handful of items for example in 1 or 2 of the product groups where the number is LGBG013 or LGBG012 where (annoyingly) the user has omitted the missing 0. We have then gone on to use LGBG0013 and LGBG0012 just to confuse matters further! But thankfully, these are isolated cases, and hopefully the work around will be simple enough.

Once I'm able to share the full list with you, you'll see that we are talking exception rather than rule. with that problem.

Hope you enjoy the rest of your afternoon, and I will have a play with the XL2BB again when I get a break
Welcome to the MrExcel board!

Here is another approach to consider. I have used a couple more helper cells but it should work for any length prefix or numerical part (within reason ;))
I have assumed data will not go beyond row 1000 but you can easily change that in the B4 formula if required.
Even if there are no blanks in the data below row 8, this will still report 1 available, my reasoning being that in such circumstance the 'next' will be whatever comes after the last entry in the list.
This will fail if cell A9 is deleted but hopefully the user would easily see that is the first available.

One further assumption is that you have the SEQUENCE function in your version of Excel 365. If not an adjustment can be made.

JimmyLGS 2020-07-28 1.xlsm
AB
1
2Count9
3Available1
4PreviousLG0004
5PrefixLG
6NextLG0005
7
8Item No.
9LG0001
10LG0002
11LG0003
12LG0004
13
14LG0006
15LG0007
16LG0008
17LG0009
18LG0010
19
Sheet1
Cell Formulas
RangeFormula
B2B2=COUNTA(A:A)-COUNTA(A1:A8)
B3B3=MAX(COUNTBLANK(A9:INDEX(A:A,MATCH("zzz",A:A))),1)
B4B4=INDEX(A9:A1000,MATCH(TRUE,A9:A1000="",0)-1)
B5B5=LEFT(B4,AGGREGATE(15,6,FIND(SEQUENCE(10,,0),B4),1)-1)
B6B6=B5&TEXT(SUBSTITUTE(B4,B5,"")+1,REPT(0,LEN(B4)-LEN(B5)))



I think that we have all assumed the Item numbers will be kept neatly in order other than any deletions. That is, there is no check in mine for example that LG0005 does not already exist somewhere else in the range.

Hi Peter

Thank you for your response. I feel I should clear something up though, because I think your formula offering will be affected by this (as Jason's has been) and it's my fault for not making it clearer in the first instance.

In my original example, the 'current available' is a bit of a red herring. It relates to data in column F which is the current 'status' of the item. This is the current status of the item on our system, be it available, on hire, scrapped, lost etc etc.

Ideally, any formula for what I want to achieve should not factor the current available field in to it. The 'Numbers allocated' field is a simple COUNTA formula that I've implemented.

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.

As for the tidiness of the numbers that is a fair assumption, and in most instances they are tidy. I've mentioned a few anomalies in my response to Jason, but thankfully these are the exception.

In its most simplistic terms what I am hoping to achieve is that if I have a list of 0001-0150 in column A (with a varying alphabetic 3or4 digit sequence at the beginning which will always start LG) I would like a cell (Next, B6) to tell me what is the next available number in that sequence. If there are gaps in the sequence, it would be the next number in the sequence for the first blank. If there are no gaps in the sequence, it would show LGxx0151, in this example, as there are no gaps and the next in the sequence will be 0151.

Ideally, the formula needs to be adaptable easily enough when creating the next sheet, as I am using 'move or copy' on the previous sheet and selecting (move to end) & 'create copy'.

Hopefully that makes sense, and will save any more confusion

Thank you all so far

Jimmy
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,826
Office Version
  1. 365
Platform
  1. Windows
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
.. and that is what my suggestion does. Can you give examples of where/how it fails so that I can try and correct if there is a problem.
My suggestion does not use the 'Available' value it is just there because you had it. :)

JimmyLGS 2020-07-28 1.xlsm
AB
1
2Count2
3Available1
4PreviousLGBL0002
5PrefixLGBL
6NextLGBL0003
7
8Item No.
9LGBL0001
10LGBL0002
11
12
LGBL
Cell Formulas
RangeFormula
B2B2=COUNTA(A:A)-COUNTA(A1:A8)
B3B3=MAX(COUNTBLANK(A9:INDEX(A:A,MATCH("zzz",A:A))),1)
B4B4=INDEX(A9:A1000,MATCH(TRUE,A9:A1000="",0)-1)
B5B5=LEFT(B4,AGGREGATE(15,6,FIND(SEQUENCE(10,,0),B4),1)-1)
B6B6=B5&TEXT(SUBSTITUTE(B4,B5,"")+1,REPT(0,LEN(B4)-LEN(B5)))
 

JimmyLGS

New Member
Joined
Jul 28, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
.. and that is what my suggestion does. Can you give examples of where/how it fails so that I can try and correct if there is a problem.
My suggestion does not use the 'Available' value it is just there because you had it. :)

JimmyLGS 2020-07-28 1.xlsm
AB
1
2Count2
3Available1
4PreviousLGBL0002
5PrefixLGBL
6NextLGBL0003
7
8Item No.
9LGBL0001
10LGBL0002
11
12
LGBL
Cell Formulas
RangeFormula
B2B2=COUNTA(A:A)-COUNTA(A1:A8)
B3B3=MAX(COUNTBLANK(A9:INDEX(A:A,MATCH("zzz",A:A))),1)
B4B4=INDEX(A9:A1000,MATCH(TRUE,A9:A1000="",0)-1)
B5B5=LEFT(B4,AGGREGATE(15,6,FIND(SEQUENCE(10,,0),B4),1)-1)
B6B6=B5&TEXT(SUBSTITUTE(B4,B5,"")+1,REPT(0,LEN(B4)-LEN(B5)))

Peter I confess, I wrongly assumed that your offering relied also on the available status and didn't even try it, apologies.

I've now tried your formulas, and it looks as though that has worked a dream, thanks so much!!! :love: :love: 6 groups down, only another 74 to go.....Fancy creating a macro that will do it all for me?! lol ;)

@Jason, you can save yourself the heartache after your shift now! Thank you very much for your help also

A very positive first experience on the forum, and will absolutely post again

Thank you all

Jimmy
 

Watch MrExcel Video

Forum statistics

Threads
1,113,903
Messages
5,544,966
Members
410,646
Latest member
jojoseb
Top