Simple formula to sort text?

[stuckagain22]

A few months back I found a simple formula for sorting a column of text data into another column which would display it alphabetically. The formula was the most simplest text sorting formula I have ever seen. If I recall it just used the SMALL function and also had "<=" in it. (It was not an array, and I don't think it even used the ROW function (but I might be wrong on that?) I have it in a spreadsheet at work using it, but I will not be back at the office for over 1 week.

I have spent 20 minutes scouring the internet trying to find the formula, but I am not able to find it. If someone knows what this formula might be, could you please let me know. Thank you.

 

[Akashwani]

Hi,

I'm no expert, but I wouldn't have thought this was possible without using an array formula, unless you are using a "Helper" column of course!!
These are array formulas...

Sort a range from A to Z using array formula in excel | Get Digital Help - Microsoft Excel resource

Unique distinct list from a column sorted A to Z using array formula in excel | Get Digital Help - Microsoft Excel resource


Excel Magic Trick 759: Array Formula To Sort List & Remove Duplicates - Dynamic Named Range - YouTube

Or, using a "Helper" column...

Excel Magic Trick 763: Formula to Sort Words (including Duplicates) and extract data - YouTube

Now this has been bumped up, perhaps one of the formula geniuses may come across the post and advise if this is possible without using a "Helper" column and array formulas, and provide an amazing solution!

Ak

 

[stuckagain22]

I will get someone from work to e-mail me the spreadsheet tomorrow. Its just uses an amazing small formula and it is not an array (which is why I prefer using it).

 

[ramananhrm]

Assuming the range is A1:A7 (Non-sorted list)

The first option is used with Small function.

Option 1:
Copy paste this function with Ctrl + Shift + Enter (Drag the formula till B7)

=INDEX($A$1:$A$7,MATCH(SMALL(COUNTIF($A$1:$A$7,"<="&$A$1:$A$7),ROWS($B$1:B1)),COUNTIF($A$1:$A$7,"<="&$A$1:$A$7),0))


Option 2:
Copy paste this function with Ctrl + Shift + Enter (Drag the formula till B7)

=INDEX($A$1:$A$7,MATCH(ROWS($B$1:B1),COUNTIF($A$1:$A$7,"<="&$A$1:$A$7),0))