hi lo from a text range

[LtCmdrData]

Thanks to all for reading this. I have been struggling for a long while to work out an equation to return the highest or lowest text value by alphabetical order. The reason behind this is i have to present the high and low for each colomn in a spreadsheet of some size and having to manually sort each colomn is tedious to say the least. Esssentially if you are looking at a list of a,b,d,e,z,c it will return "a" as the lowest and "z" as the highest, naturally the idea of a single alpha charachter is an example, normally i am dealing with alphanumeric where the numeric should be considered lower than the alpha. I hope i am making sense and fingers crossed someone knows a way round this.

Thanks in advance

 

[AnotherHiggins]

Hi, Data. How's Spot?

The only way I can think of to really do this is to sort the data first, then you can find the topmost or bottommost cell value.

To get the value from the bottom row (which could be either max or min, depending on whether you sort ascending or descending), you'd use:
[tab][COLOR=blue white]=INDEX(A:A, COUNTA(A:A))[/color]

For the value from the top row (which could be either max or min, depending on whether you sort descending or ascending) - assuming you have a header row - you could just use:
[tab][COLOR=blue white]=A2[/color]


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

"...having to manually sort each colomn is tedious to say the least"

Turn on your macro recorder and then select and sort ONE COLUMN.

Turn off the recorder. alt+F11 to toggle between the sheet and VB editor. Copy your recorded code.

Post back in Forum707 for advice on customizing your newly recorded macro. Be prepared to describe the data on the sheet. Posting a sample of the data (multiple columns) would be helpful. It is important to know if your columns of data are contiguous with the other columns of data.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bluedragon2]

You can change the first character to =code(A1) and sort on the code since numbers have lower codes then letters. The only thing is if you have upper and lower case, they have different ranges.

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...