Help find MAX value in one column when numeric & alph-numeric values are present
Below is a sample of the values below. Notice that 3 entries begin with "RD". How can I find the MAX value that begins with "RD". The RD-values will always end with 4 digits.
The formula needs to work even if the column is sorted in different ways. Thanks for your time.
20586
RD0001
20588
20589
20590
20591
20592
20593
20594
20595
RD0002
20597
20598
20599
20600
RD0003
20602
20603
20604
Last edited by KevinAB; 05-01-2013 at 10:44 AM. Reason: added 2nd line
Try this:
Confirmed with Ctrl-shift-enter
Please Login or Register to view this content.
Quang PT
@bebo021999--the formula returned "RD0000"
Another bit of info, I hope that for the range I can use A:A. However, A:A contains 2 headers, one of which is merged cells--see image below.
EXCEL_MAX query 5-1-13.JPG
UPDATE: I forgot to pres Ctrl+Shift+Enter. After doing so, the formula works. Thank you. Now, I will try it in the real worksheet that is seen the image link above.
Last edited by KevinAB; 05-01-2013 at 10:59 AM.
Have you confirmed the formula input by holding both Ctrl-Shift, then hit Enter? I it is done properly, you can see a pair of curve brackets {} in the formula bar.
Yes, after pressing Ctrl+Shift+Enter, it work properly. I then tried the formula on A:A, but it doesn't work. I think the formula fails when blank cells are present. Can the formula be tweaked to search column A?
With A:A may cause your computer slow down a bit.
Try:
Please Login or Register to view this content.
That also works. Thank you very much. I have not seen a formula like that before. It is smart engineering
It did slow things down a bit, but not enough to be a problem.
You are welcome and thanks for the feedback.
Is it time to mark it SOLVED?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks