Max and Min Number with text

[Guest]

I want to get the max and min number for "numbers only", "numbers with A"
,numbers with B", numbers with C", numberd with D", of the following list:
120
2
48
54
6
1A
5A
10A
200A
58A
60A
10B
150B
200B
13C
69C
1C
56D
23D
7D
150D

Can you get the function please and hoe to apply it in VBA?

Thanks in advance.
Maperalia.

 

[Bob Phillips]

The MAX of numbers is simple, = MAX(A1:A21), MIN is the same.

The letters is a bit more tricky

=MAX(IF(RIGHT(A1:A21)="A",--(LEFT(A1:A21,LEN(A1:A21)-1))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Same for MIN and other letters.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Bob;
Thanks for the information. I setup the formula in this way:

=IF(A1=0,"",MIN(IF(RIGHT($A$1:$A$30000)="A",--(LEFT($A$1:$A$30000,LEN($A$1:$A$30000)-1)))))

It is working Ok! but took around 20 min to make the calculation for the
data I picked up.

I wonder if there is any way if you can help me to get the statement to make
it run as a VBA and make it until last row is empty because as you see in my
formula I am calculating for 30,000 row and I am concern if my data exceed
this number and I have to change the formula all the time.

Thanks in advancew.
Maperalia