software

I want to get the max and min number for quot;numbers onlyquot;, quot;numbers with Aquot;
,numbers with Bquot;, numbers with Cquot;, numberd with Dquot;, 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.

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)=quot;Aquot;,--(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)

quot;maperaliaquot; gt; wrote in message
...
gt; I want to get the max and min number for quot;numbers onlyquot;, quot;numbers with Aquot;
gt; ,numbers with Bquot;, numbers with Cquot;, numberd with Dquot;, of the following list:
gt; 120
gt; 2
gt; 48
gt; 54
gt; 6
gt; 1A
gt; 5A
gt; 10A
gt; 200A
gt; 58A
gt; 60A
gt; 10B
gt; 150B
gt; 200B
gt; 13C
gt; 69C
gt; 1C
gt; 56D
gt; 23D
gt; 7D
gt; 150D
gt;
gt; Can you get the function please and hoe to apply it in VBA?
gt;
gt; Thanks in advance.
gt; Maperalia.
Bob;
Thanks for the information. I setup the formula in this way:

=IF(A1=0,quot;quot;,MIN(IF(RIGHT($A$1:$A$30000)=quot;Aquot;,--(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

quot;Bob Phillipsquot; wrote:

gt; The MAX of numbers is simple, = MAX(A1:A21), MIN is the same.
gt;
gt; The letters is a bit more tricky
gt;
gt; =MAX(IF(RIGHT(A1:A21)=quot;Aquot;,--(LEFT(A1:A21,LEN(A1:A21)-1))))
gt;
gt; which is an array formula, it should be committed with Ctrl-Shift-Enter, not
gt; just Enter.
gt;
gt; Same for MIN and other letters.
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;maperaliaquot; gt; wrote in message
gt; ...
gt; gt; I want to get the max and min number for quot;numbers onlyquot;, quot;numbers with Aquot;
gt; gt; ,numbers with Bquot;, numbers with Cquot;, numberd with Dquot;, of the following list:
gt; gt; 120
gt; gt; 2
gt; gt; 48
gt; gt; 54
gt; gt; 6
gt; gt; 1A
gt; gt; 5A
gt; gt; 10A
gt; gt; 200A
gt; gt; 58A
gt; gt; 60A
gt; gt; 10B
gt; gt; 150B
gt; gt; 200B
gt; gt; 13C
gt; gt; 69C
gt; gt; 1C
gt; gt; 56D
gt; gt; 23D
gt; gt; 7D
gt; gt; 150D
gt; gt;
gt; gt; Can you get the function please and hoe to apply it in VBA?
gt; gt;
gt; gt; Thanks in advance.
gt; gt; Maperalia.
gt;
gt;
gt;