A few days ago I solved a problem that I’ve been working on for more than five years: How to create a multi-cell array formula that returns a sorted list of text entries. It’s easy enough to do this with numeric entries, but the text solution has eluded me.
Range A2:A21 is named data. Select C2:C21 and type (or paste) the formula below. Then press Ctrl+Shift+Enter (rather than Enter).
Text entries made in the data range appear in the C2:C21 range in alphabetical order.
Caveat: It doesn’t work for numeric entries, or duplicated text entries.
If you’d like to get rid of the #N/A display, wrap it up in an IF function:
Here’s how to do it using the new IFERROR function (and this may be the best reason yet to upgrade to Excel 2007):
Why did it take me so long to figure this out? I was fixated on an entirely different approach — one that I’m convinced would never work. I hadn’t thought about it for about a year, but then all of a sudden it came to me.
I can’t think of any good reason to actually use this, but I feel pretty good about figuring it out.