Do you know a way in Excel to "calculate" by formula a list of unique values ?
E.g: a vertical range contains values "red"
, "blue"
, "red"
, "green"
, "blue"
, "black"
and I want to have as result "red
, "blue"
, "green"
, "black"
+ eventually 2 other blank cells.
I already found a way to get a calculated sorted list using SMALL or LARGE combined with INDEX, but I'd like to have this calculated sort as well, WITHOUT USING VBA.
Best Answer
Ok, I have two ideas for you. Hopefully one of them will get you where you need to go. Note that the first one ignores the request to do this as a formula since that solution is not pretty. I figured I make sure the easy way really wouldn't work for you ;^).
Use the Advanced Filter command
Using Formulas (note that I'm building on Locksfree solution to end up with a list with no holes)
This solution will work with the following caveats:
Here is the summary of the solution:
And here is a step by step example:
Hope this helps....