cells contain hidden values

[davidw]

I have a worksheet that has a function that compares a cell with a range. When the item in cell cannot be found in the range it puts the name down. This tells me I need to add this name to another list. However there are hidden values in the cells which stop me from sorting the data. Can you please help. I have attached a file containing the problem. Thanks in advance.

[mdmackillop]

If you are trying to sort F, it won't work because of the formulae. What end result are you looking for? It's not clear.

[davidw]

I tried to copy value only with no formula but still could not sort the way i wanted. Here is what I want.

column
H
Daniel
John




and nothing else in that column.

[mdmackillop]

Try this, entered as an array formula

=INDEX(A:A,SMALL(IF(COUNTIF(B:B,A$5:A$500)=0,ROW(A$5:A$500),1000),ROW()-4))

The column H solution is more flexible.