Do you know a way in Excel to "calculate" by formula a list of unique values ?
E.g.: a 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.
views:
1739answers:
3
A:
You could use COUNTIF to get the number of occurence of the value in the range . So if the value is in A3, the range is A1:A6, then in the next column use a IF(EXACT(COUNTIF(A3:$A$6, A3),1), A3, ""). For the A4, it would be IF(EXACT(COUNTIF(A4:$A$6, A3),1), A4, "")
This would give you a column where all unique values are without any duplicate
Locksfree
2009-09-15 22:20:17
hmmm, turns out to be a flaw in my solution. It doesn't work for a list like "black, black, blue, blue, pink, yellow". Should be able to fix it but I have to do a little work first ;^). I'll update shortly.
Drew Sherman
2009-09-17 01:25:53
ok, fixed up the solution and I now believe it works for all cases. Edited in place above.
Drew Sherman
2009-09-17 15:55:15
That seems a valid solution. in the meantime, I had found something using the LARGE or SMALL functions first to sort the items. But my solution is only working with numbers.
iDevlop
2009-09-18 12:17:33
A:
This site describes how to get a sorted list by formula without having to sort the original list http://www.get-digital-help.com/2009/04/14/create-a-unique-alphabetically-sorted-list-extracted-from-a-column/
guitarthrower
2010-02-18 17:22:54