Function to pull out data from one column to another without duplicates

[hokeyplyr48]

Here is what i'm working with:

http://img90.imageshack.us/my.php?im...uestionas8.jpg

what i want it to do is pull out the stuff in column C and put it in column F. But i don't want it to duplicate it at all. So for instance in this case, submarket 1 would be cary-morrisville-apex / submarket 2: northwest wake / submarket 3: north wake etc.

would this involve a VLOOKUP function but an if then function as well? I'm not really sure how to go about it.

thanks in advance!

[rylo]

Hi

Assuming that your data is in the range C5:C12 and your formula is going into F4, then try this array entered (ctrl, shift, enter) formula.

=IF(ROW()-3>SUM(1/COUNTIF($C$5:$C$12,$C$5:$C$12)),"",INDEX(C:C,SMALL(IF(MATCH($C$5:$C$12,$C$5:$C$12,0)=ROW($C$5:$C$12)-4,ROW($C$5:$C$12),""),ROW()-3)))

rylo

[hokeyplyr48]

i copied and pasted it in and it just gives me a blank cell.
what is it supposed to return? because i honestly didn't understand any of that

[ravishankar]

Hi
Select C4:C12 goto DATA menu > filter >advanced filter > tick copy to another location > Copy to : F3 > tick unique records only > click OK
Ravi

[hokeyplyr48]

I tried what you said and it keeps giving me the error "The extract range has a missing or illegal field name."

could this be due to the fact that column B is pulling from a column on the first worksheet. So technically wouldn't this be pulling the formulas and not just their value?

[rylo]

Hi

Did you array enter the formula. Hold down the ctrl and shift keys, then press enter. If it is entered correctly the formula should be enclosed in curly brackets {}.

rylo