Unique values

[Patcheen]

ive a list of names in c5:c500 which appear numerous times. what i am trying to do is to get the unique value only once so if paddy appears 5 times in coulum "A" then it will appear once once in column "x". X5:x500 is where i want the unique values to appear

i do not want to use any other way of doing this as in filter etc etc. im only looking for a formula to do this.

TIA

 

[Starnub]

This link should walk you through the process, it explains it much better than I could.

 

[Patcheen]

tried all that last night and none of it worked but thank you for trying yes i done the named range

 

[Marcelo Branco]

Try

Array formula in X5 copied down
=IFERROR(INDEX($C$5:$C$500,SMALL(IF(FREQUENCY(IF(C$5:C$500<>"",MATCH(C$5:C$500,C$5:C$500,0)),ROW(C$5:C$500)-ROW(C$5)+1),ROW(C$5:C$500)-ROW(C$5)+1),ROWS(X$5:X5))),"")
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[Patcheen]

thank you for trying but it leaves all cells blank from x5:x500

 

[Aladin Akyurek]

thank you for trying but it leaves all cells blank from x5:x500

Control+shift+enter means: Press down the control and the shift keys at the same time while you hit the enter key. If done properly, Excel itself puts a pair of { and } around the formula in recognition.

 

[Marcelo Branco]

thank you for trying but it leaves all cells blank from x5:x500

As Aladin said above, array formulas require a special combination of keyboard keys to be entered correctly, that is, Ctrl + Shift + Enter
If you're not familiar with array formulas take a look at
http://www.cpearson.com/excel/ArrayFormulas.aspx

M.

 

[Patcheen]

ive a list of names in c5:c500 which appear numerous times. what i am trying to do is to get the unique value only once so if paddy appears 5 times in coulum "A" then it will appear once once in column "x". X5:x500 is where i want the unique values to appear

i do not want to use any other way of doing this as in filter etc etc. im only looking for a formula to do this.

TIA[/QUOTE

Thank you works a treat for some reason it didnt work last nite but this morning it works like a treat thank you all for your input and help.......