Hi,

I have in column A, several ranges separated for, lets´s say the
character "-". I need to find the max of that range and put it in
column b, however I can not find a formula that find the maximum of a
sequence of numbers until it finds the first "-"
Of course, the numbers in columa A are the result of formulas, so the
order values etc changes with user input.

The solution should look like:

column A Column B
-
-
2 6
5
1
6
-
3 8
8
1
-
-
-
Thanks for your time
Mika

I just did one like this for a client. You need a macro to find the first -
then find the second - and max >then loop to the next group until finished.

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Mika" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
Hi,

I have in column A, several ranges separated for, lets´s say the
character "-". I need to find the max of that range and put it in
column b, however I can not find a formula that find the maximum of a
sequence of numbers until it finds the first "-"
Of course, the numbers in columa A are the result of formulas, so the
order values etc changes with user input.

The solution should look like:

column A Column B
-
-
2 6
5
1
6
-
3 8
8
1
-
-
-
Thanks for your time
Mika

Try something like this:

With your list of values in Col_A, with a column title in A1 and values, or
dashes, below"

B2:
=IF(AND(A1="-",ISNUMBER(A2)),MAX(OFFSET(A2,,,MATCH("-",$A2:$A$65536,0),1)),"")

Copy B2 down as far as you need.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Mika" wrote:

> Hi,
>
> I have in column A, several ranges separated for, lets´s say the
> character "-". I need to find the max of that range and put it in
> column b, however I can not find a formula that find the maximum of a
> sequence of numbers until it finds the first "-"
> Of course, the numbers in columa A are the result of formulas, so the
> order values etc changes with user input.
>
> The solution should look like:
>
> column A Column B
> -
> -
> 2 6
> 5
> 1
> 6
> -
> 3 8
> 8
> 1
> -
> -
> -
> Thanks for your time
> Mika
>
>

Thanks both,


Don you are right with vba is piece of cake, but not possible for this
work.

Ron, I included at the beggining of those cells a comment:
"solution proposed by Ron Coderre"


Thanks it worked like a charm !

Mika

Thanks for the feedback, Mika....I'm glad that worked for you.

***********
Regards,
Ron

XL2002, WinXP


"Mika" wrote:

> Thanks both,
>
>
> Don you are right with vba is piece of cake, but not possible for this
> work.
>
> Ron, I included at the beggining of those cells a comment:
> "solution proposed by Ron Coderre"
>
>
> Thanks it worked like a charm !
>
> Mika
>
>

>not possible for this work.
Why not

lr = Cells(Rows.Count, "a").End(xlUp).Row
c1 = 1
Do Until c1 + 1 >= lr
c1 = Range(Cells(c1, 1), Cells(lr, 1)).Find("-").Row
c2 = Range(Cells(c1 + 1, 1), Cells(lr, 1)).Find("-").Row - 1
Cells(c2, 2) = Application.Max(Range(Cells(c1, 1), Cells(c2, 1)))
c1 = Range(Cells(c2, 1), Cells(lr, 1)).Find("-").Row - 1
Loop

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Mika" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thanks both,
>
>
> Don you are right with vba is piece of cake, but not possible for this
> work.
>
> Ron, I included at the beggining of those cells a comment:
> "solution proposed by Ron Coderre"
>
>
> Thanks it worked like a charm !
>
> Mika
>

Improvement if no - at the bottom

lr = Cells(Rows.Count, "a").End(xlUp).Row
If Cells(lr, 1) <> "-" Then
Cells(lr + 1, 1) = "-"
lr = lr + 1
End If
c1 = 1
Do Until c1 + 1 >= lr
c1 = Range(Cells(c1, 1), Cells(lr, 1)).Find("-").Row
c2 = Range(Cells(c1 + 1, 1), Cells(lr, 1)).Find("-").Row - 1
Cells(c2, 2) = Application.Max(Range(Cells(c1, 1), Cells(c2, 1)))
c1 = Range(Cells(c2, 1), Cells(lr, 1)).Find("-").Row - 1
Loop

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Don Guillett" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> >not possible for this work.
> Why not
>
> lr = Cells(Rows.Count, "a").End(xlUp).Row
> c1 = 1
> Do Until c1 + 1 >= lr
> c1 = Range(Cells(c1, 1), Cells(lr, 1)).Find("-").Row
> c2 = Range(Cells(c1 + 1, 1), Cells(lr, 1)).Find("-").Row - 1
> Cells(c2, 2) = Application.Max(Range(Cells(c1, 1), Cells(c2, 1)))
> c1 = Range(Cells(c2, 1), Cells(lr, 1)).Find("-").Row - 1
> Loop
>
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> "Mika" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Thanks both,
>>
>>
>> Don you are right with vba is piece of cake, but not possible for this
>> work.
>>
>> Ron, I included at the beggining of those cells a comment:
>> "solution proposed by Ron Coderre"
>>
>>
>> Thanks it worked like a charm !
>>
>> Mika
>>
>
>

Thanks Don,

Sorry for not explaining clearly, but it is not allowed to use macros
in this spreadsheet. That´s why I didn´t post it in the program area.

Rgd
Mika