display specific next highest value from a column containing multi

[bvasquez]

Just cant seem to get it. I am working with data in F10:F100 that contains
numbers like (1,2,3,4) as well as lettered numbers like (C1,C2,C3,C4). I can
not come up with a formula that will index this column and return the next
highest C number. F9 needs to display the next highest C number. I can get
this to work with the standard numbers only (1,2,3,4). Any suggestions

 

[Gary''s Student]

To simplify the formula we will cheat and use a "helper column". Say the
data is:

C23
C43
C56
4
5
C77

in G1 enter:
=IF(LEFT(F1,1)="C",--RIGHT(F1,LEN(F1)-1),"") and copy down
we see:

C23 23
C43 43
C56 56
4
5
C77 77

In another cell enter:
=MATCH(LARGE(G:G,2),G:G) this displays 3 ( the row with the second largest)

so LARGE() gets the second largest and MATCH() ids the row where it is.

 

[T. Valko]

Are these alpha-numerics in sequential order? Like this:

1
2
C1
C2
3
4
C3
5
C4

Or, are they random:

C4
2
C1
1
3
C3

 

[bvasquez]

They are random. The C numbers I manually enter. For my application I am
using these C numbers to indicate change orders on a contract. It is
imperative that the go in a consecutive order without skipping any numbers.
They will be entered in a random order but always within the F column
starting with the range of (F10:F100). As the range (F10:F100) begins to fill
up and I expand the range to (F10:F500) and so on, I am using cell F9 to tell
me what my next consecutive C number will be. This way I ensure the C count
climbs consecutively without having to physically look through the range for
the next highest C number and possibly missing a number in between.

 

[RagDyeR]

Try this *array* formula:

=MAX(IF(LEFT(F10:F100)="C",(--RIGHT(F10:F100,LEN(F10:F100)-1))))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

They are random. The C numbers I manually enter. For my application I am
using these C numbers to indicate change orders on a contract. It is
imperative that the go in a consecutive order without skipping any numbers.
They will be entered in a random order but always within the F column
starting with the range of (F10:F100). As the range (F10:F100) begins to
fill
up and I expand the range to (F10:F500) and so on, I am using cell F9 to
tell
me what my next consecutive C number will be. This way I ensure the C count
climbs consecutively without having to physically look through the range for
the next highest C number and possibly missing a number in between.

 

[RagDyeR]

Should mention, this gives the highest "C" number that is presently in the
range.

If you would like, simply append a "+1" to the end of the formula to give
you the *next* number to use.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Try this *array* formula:

=MAX(IF(LEFT(F10:F100)="C",(--RIGHT(F10:F100,LEN(F10:F100)-1))))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

They are random. The C numbers I manually enter. For my application I am
using these C numbers to indicate change orders on a contract. It is
imperative that the go in a consecutive order without skipping any numbers.
They will be entered in a random order but always within the F column
starting with the range of (F10:F100). As the range (F10:F100) begins to
fill
up and I expand the range to (F10:F500) and so on, I am using cell F9 to
tell
me what my next consecutive C number will be. This way I ensure the C count
climbs consecutively without having to physically look through the range for
the next highest C number and possibly missing a number in between.

 

[T. Valko]

Ok, in other words you want to know what the *last* C number entered is?

1
2
C1
C2
22
4
C3
7
26

=LOOKUP(2,1/(LEFT(F10:F100)="C"),F10:F100)

Result = C3

 

[RagDyeR]

Meant to post this example (also minus extra parens):

=MAX(IF(LEFT(F10:F100)="C",--RIGHT(F10:F100,LEN(F10:F100)-1)))+1
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Should mention, this gives the highest "C" number that is presently in the
range.

If you would like, simply append a "+1" to the end of the formula to give
you the *next* number to use.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

Try this *array* formula:

=MAX(IF(LEFT(F10:F100)="C",(--RIGHT(F10:F100,LEN(F10:F100)-1))))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, CSE *must* be used when
revising the formula.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

They are random. The C numbers I manually enter. For my application I am
using these C numbers to indicate change orders on a contract. It is
imperative that the go in a consecutive order without skipping any numbers.
They will be entered in a random order but always within the F column
starting with the range of (F10:F100). As the range (F10:F100) begins to
fill
up and I expand the range to (F10:F500) and so on, I am using cell F9 to
tell
me what my next consecutive C number will be. This way I ensure the C count
climbs consecutively without having to physically look through the range for
the next highest C number and possibly missing a number in between.

 

[bvasquez]

In the following scenario you provided I would like the result in F9 to = C4

 

[Ron Coderre]

If the C-Values can be out of order,
this ARRAY FORMULA (committed with CTRL+SHIFT+ENTER,
instead of ENTER) returns the largest C-Value + 1:
F9: ="C"&(MAX(IF(LEFT(F10:F100,1)="C",--MID(F10:F100,2,99)))+1)

However, if the C-Values are ALWAYS in ascending order,
this regular formula returns the LAST C-Value + 1:
="C"&(MID(LOOKUP(2,1/(LEFT(F10:F100,1)="C"),F10:F100),2,99)+1)

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[T. Valko]

Assuming there are only numbers and "C numbers" in the range (empty cells
and formulas blanks are OK).

A few keystrokes shorter:

=MAX((LEFT(F10:F100)="C")*REPLACE(F10:F100,1,1,0))

 

[bvasquez]

C-Values can be out of order
F9: ="C"&(MAX(IF(LEFT(F10:F100,1)="C",--MID(F10:F100,2,99)))+1)
That is exactly what I am looking for. Thank you Mr. Coderre. Thank you all
for all your submittals and suggestions. By studying all submitted I have
learned an incredible amount. First time submitting....it has been an
excellent experience.

 

[T. Valko]

Assuming there are only numbers and "C numbers" in the range (empty cells
and formulas blanks are OK).

Try this array formula** :

="C"&MAX((LEFT(F10:F100)="C")*REPLACE(F10:F100,1,1,0))+1

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Ron Coderre]

You're very welcome.....I'm glad I could help.

Regards,

Ron
Microsoft MVP (Excel)