List of unique values in another spreadsheet

[stepan1987]

Hi guys,
I need some formula that would return a list of unique values from one column to a column on another sheet. Pls advice how that could be done technically?
It should be done with a formula, so if there is a new value it would be translated to the list in another sheet automatically.

Thanks a lot!

 

[Jon von der Heyden]

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 />

 

[stepan1987]

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

 

[stepan1987]

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

 

[Jon von der Heyden]

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.

 

[stepan1987]

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

 

[maximun_vader]

One way:
[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]{=INDEX($A$2:$A$8,MATCH(0,COUNTIF($B$1:B1,$A$2:$A$8),0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
[/TD]
[/TR]
</tbody>[/TABLE]

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! !!

 

[Jon von der Heyden]

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