Find unique values without using autofilter

[ukjent]

Hi

I'm trying to find a formula which returns all unique values in a column into one cell like this (autofilter is not useful);

Unique values;
Apple,Banana,Tomato

Table
Apple
Banana
Apple
Tomato
Tomato
Apple
Banana

To complecate it could also be useful with a criteria check;


Unique values with criteria 1;
Apple,Banan

Criteria Table
Criteria 1 Apple
Criteria 2 Banan
Criteria 2 Apple
Criteria 2 Tomato
Criteria 2 Tomato
Criteria 1 Apple
Criteria 1 Banan


Any ideas?

[Ron Coderre]

Excel does a poor job of concatenating the way you want. Unless your needs for that formula are very meager, you'd need to use a User Defined Function (UDF) created using VBA. If you post the maximum potential values for a cell and the number of cells that might need the formula, we might be able to offer some alternatives.

[ukjent]

There are probably no more than 5-10 values for each criteria but i have to be able to list it out for each criteria. It may be up to 35 critereas (like the list above).

I have several sheets where this has to be done but it should not be mixed together (the criteria is the same but the values from each sheet should be separeted)

Ex;

Criteria 1
Values from Sheet 1 Apple, Banana
Values from Sheet 2 Orange, Melon

Criteria 2
Values from Sheet 1 Apple, Banana, Tomato
Values from Sheet 2 Cucumber, Orange

Criteria 3
Values from Sheet 1
Values from Sheet 2 Orange, Melon


Sheet 1 Sheet 2
Criteria 1 Apple Criteria 1 Orange
Criteria 2 Banana Criteria 2 Cucumber
Criteria 2 Apple Criteria 1 Melon
Criteria 2 Tomato Criteria 3 Orange
Criteria 2 Tomato Criteria 3 Melon
Criteria 1 Apple Criteria 2 Orange
Criteria 1 Banana

[Tony Valko]

There is a very useful VBA user defined function in this thread:

http://www.excelforum.com/tips-and-t...geravatar.html

The version you want is in post #4.

After you install the module, if this is your data in the range A2:B8...

Criteria 1.....Apple
Criteria 2.....Banan
Criteria 2.....Apple
Criteria 2.....Tomato
Criteria 2.....Tomato
Criteria 1.....Apple
Criteria 1.....Banan

D2 = Criteria 1

Then, this array formula** entered in cell E2:

=concatall(IF(A2:A8=D2,B2:B8,""),", ",TRUE)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Result = Apple, Banan

[ukjent]

Thx!

This works for me!

[Tony Valko]

Good deal. Thanks for the feedback!