Thread: Unique values formula

I am tasked with porting a spreadsheet project to Excel from Google Docs. Google features a nifty formula which returns a dynamic list of unique values in a range. By unique, I mean values which appear at least once, and not those which appear only once.

If I wanted a list of unique values in column A, I would enter "=UNIQUE(A:A)" in, e.g. B1. Column B would then list the values that appear at least once in column A, dynamically.

Does Excel have such a formula? I'm using the Advanced Filter feature currently, but it doesn't appear to be dynamic.

Thank you, for any help anyone can offer with this.

Thank you both, I do appreciate it!

If I needed the formula to return results from a different sheet, how would I ask that?

I had tried to add the name at the beginning of the range, but my attempt is incorrect:

=COUNTIF(Sheet3!A$A:A1,A1)=1

What about generating this unique values list from another list of data for which the number of rows fluctuates daily. For example, you can take these formulas and place them into the cells next to your data and drag them down to pull out the uniques. However, what if the data you're pulling from changes and you don't have the ability to keep going in and dragging down the formulas?

Is there a way, without any modification to the software's settings (macros, etc...), to generate the unique values list automatically?

My project requires this to be possible.

Big log with multiple international users, must be shared, some only have Excel 2003. Want to prevent overwriting data and getting items into wrong columns, and limit some entries to their own list. Some have been keeping their own spreadsheet and then copying over into the main log, making a mess.

Am willing to work with VBA but just a beginner with it. Simple non-VBA validation doesn't prevent overwriting, nor does it work when entering by forms. What I want is to ensure Data Integrity for metrics to report progress.

Hoping to work with this over the weekend of 3-4dec11.

I'd like to re-open this thread, and dig-in a bit further in the hopes of discovering a solution to my problem.

I'm wondering if there might be a way to achieve the effect I'm looking for with a combination of formulas and "helper columns".

For instance, would there be a way to have a column return a count of the number of times a value in the same row appears (within a date range, also), and then assign another column to print the corresponding value to every "1" that appears, effectively producing a list of the unique values (the values that appear only once)?

I've attached a sample spreadsheet with an idea of what I'm getting at. Many, many thanks in advance to anyone who can help me! Note in the spreadsheet, I'm going with a monthly date parameter, so that the unique values are specific to the month. Ideally, this would be a range of months, e.g. April-July, etc.

This seems like precisely what I have been looking for. It will give me, not only the ability to count unique values, but also, another requirement, to be able to count multiple instances, but the number of instances. Many thanks!