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

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!

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

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

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

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?

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

sorry yah i forgot to do that part.
but it only spits out one name, the first one. is there a way to have this spit out all the names or something i can change the formula to in each different cell?

Hi

Copy the formula from F4, and enter in F5, F6 etc as required.


rylo

hey, i'm having a little bit of trouble with it.
i need to change the function to start at C4 instead of C5, but when i change all the $C$5's in the equation to $C$4 it gives me a #NUM error. In addition, i need to change the range to C140 because thats the max number of items that can be added to the whole sheet and that i need to make available. If i change the $C$12 to $C$140 it gives me a DIV/0 error.

any ideas?

Hi

Can you attach an example file. Zip the spreadsheet file before you try to attach to the post.

rylo

It was 140k when i zipped it and tried to add it here. I just hosted it and you can download it here:
http://rapidshare.com/files/10557126...Comps.xls.html

It's on the Sale by submarket worksheet (3rd one)

Hi

Bombed out on the download.


rylo

if that means it didn't work, try this one:
http://www.mediafire.com/?j1jnsmjvgd2

yes they're safe and do not contain any sort of virus/spyware/adware/malware/etc.

Hi

The formula in F4 should be

Please Login or Register  to view this content.

However, that won't fix things. It would appear that the INDIRECT function being used to extract the data is interferring with the formula.

Can you change
B4: =IF('2008 Apartment Comps (INPUT)'!C3="","",'2008 Apartment Comps (INPUT)'!C3)
C4: =IF(OR(B4="",'2008 Apartment Comps (INPUT)'!AH3=""),"",'2008 Apartment Comps (INPUT)'!AH3)
Copy down to row 12 (just for the test) and see if that brings back the right data.

If OK, then expand as required.

rylo

the reason i was using the indirect formula was so that when i insert rows on the main sheet, it wouldn't autocorrect the formulas on this sheet. Like when i would insert a row on the main sheet, it would smartly correct the formulas on this sheet to rematch the data. I didn't want that, but i wanted it to mirror whatever was in those cells on the first sheet regardless of inserting or removing rows.

But i did try changing the cells suggested and it did seem to work correctly.

Now what i need to do is determine how to fix the indirect problem or somehow modify this function to read the indirect functions.

any ideas?

Hi

one way would be to build your own function.

In the workbook, create a general module, and insert the code

Please Login or Register  to view this content.

Now in F4 enter the formula
=getsubmarket($C$4:$C$140,ROW()-3)
and copy down.

This will bring back a unique list, ignoring the blanks. If you wanted to, you could sort the data and have them appear alphabetically.

HTH

rylo