Excel spreadsheet formulas not updating
Column B: This formula will output 0 if the value in this line is valid (contains a non-duplicated value).
Lets say you have "red", "blue", "red", "green", "blue", "black" in column A2: A7 then put this in B2 as an array formula and copy down You could use COUNTIF to get the number of occurence of the value in the range .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 ;^).In C2 type in 1, 2 ,3 etc going down the column In D2 = OFFSET($A$1, MATCH(c2,$B$2:$B$x,0),0) - where x is the last cell Drag down, only the unique values will appear.-- put in some error checking Select the column with duplicate values then go to Data Tab, Then Data Tools select remove duplicate select 1) "Continue with the current selection" 2) Click on Remove duplicate....Drew Sherman's solution is very good, but the list must be contiguous (he suggests manually sorting, and that is not acceptable for me).
Guitarthrower's solution is kinda slow if the number of items is large and don't respects the order of the original list: it outputs a sorted list regardless.
If you wanted it to scale with an unpredictable number of rows, you could replace A772 (where my data ended) with =ADDRESS(COUNTA(A: A),1).
=IF(COUNTIF(A5:$A$772, A5)=1, A5,"") This will display the unique value at the LAST instance of each value in the column and doesn't assume any sorting.
It takes advantage of the lack of absolutes to essentially have a decreasing "sliding window" of data to count.
When the countif in the reduced window is equal to 1, then that row is the last instance of that value in the column.
I admit this isn't a viable solution for Excel users, but this approach is useful for anyone who wants the functionality and is able to use a Google spreadsheet.