PDA

View Full Version : cells contain hidden values



davidw
04-22-2010, 02:43 PM
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
04-22-2010, 03:23 PM
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
04-22-2010, 03:39 PM
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
04-22-2010, 05:01 PM
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.