how to display only unique values

zazzz, look at here.

You must have 0 in D31.
Also, in E32 is UDF function so if you copy to your workbook you must also copy code (not only function).

Originally Posted by zazzz

what's the significance of E31, where there's a red highlighted 0?

It's in D31 in my example, but that's not important...
It's related to:

Formula:

=IFERROR(INDEX($D$2:$D$30;MATCH(TRUE;INDEX(ISNA(MATCH($D$2:$D$30;D$31:D31;0));0);0));"")

Significance is that if you remove it it will count first blank cell as unique and it will put 0 at the end (try).
This 0 grant you to have only unique values without that last 0.

Originally Posted by zazzz

When you say copy code not only function, what exactly does that mean, that copying the formula wont be enough and that this is macro related? Not sure if that's what you're saying but if it is, why would that be - the formula should work all the time, no?

Correct. Above formula will work. But next formula:

Formula:

=F_conc_col_noblanks_sep_snb(D32:D42;",")

is UDF (User Defined Formula) that is not common Excel formula and if you want to use it you must copy code aswell (hit ctrl+F11 to see code).

Originally Posted by zazzz

On another note I've seen people saying something about putting in a formula by hitting control-shift-enter, how does that work? Do you do it at time of pasting, or after when trying to run, smth else?

It's called array formula. You must hit ctrl+shift+enter (CSE) when you entering formula and you will see curely brackets {=formula} around it.
It's used when you working with array, and not single value.
For example, in this example:

1	=SUM(2*A1:A4)
2	=SUM(2*A1:A4)
3	
4

First formula, with normal enter, will return value 2 (=2*A1)
Second (looking same) entered with CSE will return 20 (=2*A1+2*A2+2*A3+2*A4) because ti will work with all array (try).

Hope it's clearer now.