Find next avaliable number in out of sequence column

**Kudos247** · 05-23-2008, 01:24 AM

Hi All,

I need to be able to click on a button that will look at the contents of a column and return the next avaliable number in numerical order, even if the contents of the column are out of numerical order.

Example:

In column B

RFW0009
RFW0002
RFW0001

RFW0004
RFW0008

RFW0010
RFW0003
RFW0007
RFW0005

RFW0006

So I need something that will look at the column above and return a result of RFW0011.

I hope that makes sense and someone can help me out.

Cheers

**dominicb** · 05-23-2008, 04:37 AM

Good morning Kudos247

...and welcome to the forum!!

If your range occupies, say, B1:B10 then something like this should help :
=LEFT("RFW0000",7-LEN(MAX(VALUE((RIGHT(B1:B10,4))))+1)) & MAX(VALUE((RIGHT(B1:B10,4))))+1
This is an array formula, so Ctrl + Shift + Enter to commit.

HTH

DominicB

**Jazzer** · 05-23-2008, 05:35 AM

Hi,

how about this?

Please Login or Register  to view this content.

EDIT: Sorry, too hasty. That doesn't work.

but this does:

Please Login or Register  to view this content.

It's also an array formula, so ctrl+shift+enter to commit.

- Asser

**VBA Noob** · 05-23-2008, 09:00 AM

Another option

Just enter the numbers like 1 to 10 then custom format the cells as

"RFW"0000

Then use this formula to return the next value...again format cell as above

=LARGE(A:A,1)+1

**daddylonglegs** · 05-23-2008, 11:24 AM

a variation.......

=TEXT(MAX(SUBSTITUTE(0&B1:B100,"RFW","")+1), "RFW0000")

confirmed with CTRL+SHIFT+ENTER

**Kudos247** · 05-25-2008, 09:21 PM

Originally Posted by VBA Noob

Another option

Just enter the numbers like 1 to 10 then custom format the cells as

Then use this formula to return the next value...again format cell as above

Thanks Guys,

I ended up using VBA Noob's idea which was the most simplistic but worked a treat.

Thank you all for your help. Much appreciated.

Cheers

Find next avaliable number in out of sequence column

LinkBack

Thread Tools

Rate This Thread

Display

Find next avaliable number in out of sequence column

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions