Sorting alphabetically by function

[Pauljj]

As with numbers you can sort using the function LARGE, is there a way you can do this alphabetically ??

 

[Aladin Akyurek]

As with numbers you can sort using the function LARGE, is there a way you can do this alphabetically ??

See my post in:

http://www.mrexcel.com/board2/viewtopic.php?t=209369

 

[Domenic]

Here's another way...

Assuming that A2:A10 contains the data...

B2:

=INDEX(A2:A10,MATCH(0,COUNTIF(A2:A10,">"&A2:A10),0))

B3, copied down:

=IF(COUNTIF(A$2:A$10,"<"&B2),INDEX(A$2:A$10,MATCH(COUNTIF($A$2:$A$10,">="&B2),COUNTIF(A$2:A$10,">"&A$2:A$10),0)),"")

Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[bosco_yip]

Or trying……

=IF(ROWS(B$2:B2)<=COUNTA(A:A),LOOKUP(2,1/(COUNTIF($A$2:$A$10,">"&$A$2:$A$10)=ROWS(B$2:B2)-1),$A$2:$A$10),"")

Regards
Bosco

 

[Domenic]

Actually, my solution returns a list of unique values, sorted alphabetically, in descending order. However, I should have offered the following solution instead...

B2, copied down:

=INDEX(A$2:A$10,MATCH(SMALL(COUNTIF(A$2:A$10,">"&A$2:A$10),ROWS(B$2:B2)),COUNTIF(A$2:A$10,">"&A$2:A$10),0))

...which lists duplicate values as well.

Hope this helps!

 

[Aladin Akyurek]

Here's another way...

Assuming that A2:A10 contains the data...

B2:

=INDEX(A2:A10,MATCH(0,COUNTIF(A2:A10,">"&A2:A10),0))

B3, copied down:

=IF(COUNTIF(A$2:A$10,"<"&B2),INDEX(A$2:A$10,MATCH(COUNTIF($A$2:$A$10,">="&B2),COUNTIF(A$2:A$10,">"&A$2:A$10),0)),"")

Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

Some notes on the above formula system:

1] It cannot cope with numbers (both pos and neg) in the range to sort.
2] It eliminates duplicates under the assumption of like behavior as Data|Sort > Descending.
3] Although it's less volatile than the system in the link I quoted and it has lesser usage of cell memory, Full Calc and Microseconds per formula is higher.

N.B. I succeeded to eliminate the range housing rankings with Rank from the system in the link I quoted, the revised system becomes less volatile but Microseconds per formula doubles and it cannot cope with neg numbers in the range to sort.

 

[vane0326]

Input formula in cell b2 and copied down.


=IF(COUNTA($A$1:$A$100)>ROW()-ROW($A$1),INDEX($A$1:$A$100,MATCH(LARGE(COUNTIF($A$1:$A$100,">"&$A$1:$A$100),ROW()-ROW($A$1)+1),COUNTIF($A$1:$A$100,">"&$A$1:$A$100),0)),"")

Formula is an-array must hold down:

Ctrl,Shift,Enter


Edit: I changed the formula because it did not account for duplicates this formula will.

 

[Domenic]

Here's another way...

Assuming that A2:A10 contains the data...

B2:

=INDEX(A2:A10,MATCH(0,COUNTIF(A2:A10,">"&A2:A10),0))


B3, copied down:

=IF(COUNTIF(A$2:A$10,"<"&B2),INDEX(A$2:A$10,MATCH(COUNTIF($A$2:$A$10,">="&B2),COUNTIF(A$2:A$10,">"&A$2:A$10),0)),"")

Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

Some notes on the above formula system:

1] It cannot cope with numbers (both pos and neg) in the range to sort.

I was under the impression that the range would contain only alpha-characters, but of course I could be wrong.

2] It eliminates duplicates under the assumption of like behavior as Data|Sort > Descending.

Yes, for some reason I was thinking unique values, hence my follow-up post...

3] Although it's less volatile than the system in the link I quoted and it has lesser usage of cell memory, Full Calc and Microseconds per formula is higher.

Interesting, although I wasn't aware that it was at all volatile. I don't see any volatile functions, unless I'm missing something. Can you please shed some light?

N.B. I succeeded to eliminate the range housing rankings with Rank from the system in the link I quoted, the revised system becomes less volatile but Microseconds per formula doubles and it cannot cope with neg numbers in the range to sort.

Very interesting...

 

[Aladin Akyurek]

Here's another way...

Assuming that A2:A10 contains the data...

B2:

=INDEX(A2:A10,MATCH(0,COUNTIF(A2:A10,">"&A2:A10),0))


B3, copied down:

=IF(COUNTIF(A$2:A$10,"<"&B2),INDEX(A$2:A$10,MATCH(COUNTIF($A$2:$A$10,">="&B2),COUNTIF(A$2:A$10,">"&A$2:A$10),0)),"")

Note that both formulas need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

Some notes on the above formula system:

1] It cannot cope with numbers (both pos and neg) in the range to sort.

I was under the impression that the range would contain only alpha-characters, but of course I could be wrong.

Ignoring generality is admissible if the system has a signicant superiority over one that is (more) general.

2] It eliminates duplicates under the assumption of like behavior as Data|Sort > Descending.

Yes, for some reason I was thinking unique values, hence my follow-up post...

I didn't check the modification.

3] Although it's less volatile than the system in the link I quoted and it has lesser usage of cell memory, Full Calc and Microseconds per formula is higher.

Interesting, although I wasn't aware that it was at all volatile. I don't see any volatile functions, unless I'm missing something. Can you please shed some light?

Quoting from Charles Williams's site:

"FastExcel measures Workbook Volatility by comparing the time for a Recalculation to the time for a full calculation, and allows you to measure worksheet volatility by comparing the sheet recalculate time with the sheet full calculate time."

Volatile functions aggravates the volatility score for:

"A Volatile Function in a formula in a cell is always recalculated at each recalculation even if it does not appear to have any changed precedents."

 

[Pauljj]

I am amazed and very grateful for all your responses, thank you so much