Unique entries

Posted by KC Goewey on October 26, 2000 6:35 AM

I have a list of names that I would like to pull out all unique entries from. I tried =INDEX(data,SMALL(IF(MATCH(data,data,0)=ROW(INDIRECT("1:"&ROWS(data))),MATCH(data,data,0),""),ROW(INDIRECT("1:"&ROWS(data))))), but I think that my named range is not working correctly. I tried replacing the range name with the actual range with no luck. Any ideas? Thanks!
KC

Posted by Neil on October 26, 2000 2:24 PM

Try Data, Filter, Advanced Filter and click on the unique records only

Posted by Celia on October 26, 2000 6:11 PM

KC
In what way is it not working? It works for me.
You need to select a single column range which is the same size as "data" and enter the formula by Ctrl+Shift+Enter. Also, it will not work if "data" contains any blank cells.
Celia

Posted by KC Goewey on October 27, 2000 7:21 AM

That might be the problem Celia, I have blank cells at the end. Because the range will change each day, how do I have this formula take that into account without editing it each day? I could have between 2-800 entries by the end of the month. I went back to edit my range to the exact size and tried the formula. If I copy the formula down, it only returns a single name.

Posted by Celia on October 27, 2000 4:56 PM

KC
I don't think this formula can be copied or filled down. You have to select all the cells first and then press Ctrl+Shift+Enter.
To avoid having to change your named range, put the following formula in the Refers To box :-

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

For this to work, there must be no blank cells in the "data" range.

Celia

Posted by KC Goewey on October 30, 2000 6:31 AM

Thanks Celia, I'll try that. :)