List of unique values in another spreadsheet

One way:
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="background-color: #BFBFBF;;">List</td><td style="background-color: #BFBFBF;;">Unique</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">a</td><td style=";">a</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">f</td><td style=";">f</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">g</td><td style=";">g</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">r</td><td style=";">r</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">f</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">g</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">a</td><td style="text-align: right;;">#N/A</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B2</th><td style="text-align:left">{=INDEX(<font color="Blue">$A$2:$A$8,MATCH(<font color="Red">0,COUNTIF(<font color="Green">$B$1:B1,$A$2:$A$8</font>),0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

Is it suitable for excel 2007? I copied the formula with respective arrays - it always returns nil somewhy..

Even if I try it on the same sheet it returns me a nil.. Are there any other solutions?

Yes it should work.

Breaking it down:

$A$2:$A$8 - This should be the list of the values that you want to extract unique values for. If you are pulling unique vals into a separate sheet then you need to qualify the sheet name (e.g. Sheet1!$A$2:$A$8).

$B$1:B1 - This is a reference to the values that have already been returned (so that they are not repeated). If you are pulling unique vals into a separate sheet then this will reference that separate sheet. It must always be a reference to the same column that you are returning the results to. The first reference must be anchored ($B$1) and the second reference must be relative (B1). That way as you copy the formula down the range reference will expand. Note that the start of the range reference is one cell above where you being your results (i.e. results start in B2).

If this isn't clear then post back with details of the range that houses the values and the sheet name.

The thing was with the cell address in the second sheet, now it all works great, thanks!

Jon von der Heyden said:

One way:

Array Formulas Cell Formula B2 {=INDEX($A$2:$A$8,MATCH(0,COUNTIF($B$1:B1,$A$2:$A$8),0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Click to expand...

This is like a million years old, and you might never read this, but I wanted to thank you for providing this formula. It helped me a lot in my work.

Many thanks! !!

You're very welcome I wish I could take credit for that formula; if memory serves I first learned this from Andrew Poulsom.