I have 4 columns and 1 row.
Entries in the 4 columns range for 1A, 1B, 1C, 1D, 1E, 2A, 2B, 2C, 2D, 2E THRU....................5A, 5B, 5C, 5D, 5E. Note the number is always before the letter.
I want to search the 4 columns and return the highest alphanumeric value in the 5th column.
The Lowest value in the range is 1A and Highest value in the range is 5E.
Example: the 4 columns contain 1E, 2B, 4A and 4C. I want search the 4 columns and return the result "4C" in the 5th column.
Thanks,
Tom
Please try at E1 and drag down
=INDEX(A1:D1,MATCH(0,INDEX(COUNTIF(A1:D1,">"&A1:D1),),))
Tom,
Please update your profile to clearly indicate what version(s) of Excel you are using ... ie. 2003, 2007, 2010, 2013, 2016, 365.
Forum members tailor solutions with this in mind. There are additional ways to solve this depending upon Excel versions. Thanks.
Dave
Also
will the numbers always be single digits? Will there ever be say ... a 14C?Note the number is always before the letter.
With that in mind tryFormula:
Please Login or Register to view this content.
Last edited by FlameRetired; 10-19-2018 at 03:28 PM.
Hello Bo_Ry,
OK that works fine. Thanks for the very quick response and the formula.
However, when I insert the formula in the cell D1 and drag it down it doesn't work in all cells? Odd and I can't see why?
I want to attach a file, but the paperclip icon doesn't seem to work? Just get a white rectangle, not a Browse box
Tom
It hasn't worked for years.I want to attach a file, but the paperclip icon doesn't seem to work? Just get a white rectangle, not a Browse box
To attach a file to your post: (Please no pics or screenshots ... saves retyping data.),
- be sure to desensitize the data
- click “Go Advanced” (next to Post Quick Reply – bottom right),
- scroll down until you see “Manage Attachments”, click that,
- click “Browse”.
- select your file(s)
- click “Upload”
- click “Close window”
- click “Submit Reply”
The file name will appear at the bottom of your reply.
Hello Bo_Ry,
OK that works fine. Thanks for the very quick response and the formula.
However, when I insert the formula in the cell D1 and drag it down it doesn't work in all cells? Odd and I can't see why?
See attached and Row 14 for example
Tom
Edited
Try this modification to Bo_Ry's formula.
Formula:Please Login or Register to view this content.
Or use this formula from Post #4.Formula:Please Login or Register to view this content.
Last edited by FlameRetired; 10-19-2018 at 05:58 PM.
Thanks everyone. It works.
FlameRetired,
The DEC formula works great.
One other thing. I mis quored the range. It starts at 0a not 1A.
So range is
0A, 0B, 0C, 0D, 0E thru...... 5A, 5B, 5C, 5D, 5E. 5E IS THE MAX ALPHA NUMERIC IN THE RANGE.
The DEC formula return the highest value in range starting 1A, but only return the letters A, B, C, D, E IS THERE IS A "0" ZERO in front of the letter.
How do I fix this to return e.g. 0C?
=dec2hex(max(index(hex2dec(a1:d1),0)), 2)
Entia non sunt multiplicanda sine necessitate
Thanks. It works if I insert + in front of a1 i.e +a1
Please see post #3 and update your profile accordingly. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Don't know how to do that?
Above your first post click 'Forum Actions' then click 'Edit Profile'.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks