display specific next highest value from a column containing multi

  • Thread starter Thread starter bvasquez
  • Start date Start date
B

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
 
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.
 
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
 
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.
 
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.
 
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.
 
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
 
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.
 
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)
 
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))
 
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.
 
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)
 
Back
Top