Simon Lloyd
04-01-2007, 03:22 PM
Hi all, i use this array formula for finding unique items in a list, however there is one item "No Data" that i want to ignore can anyone give a fix for it?
{=IF(ISERR(INDEX(DataResult,SMALL(IF(MATCH(DataResult,DataResult,0)=ROW(IND IRECT("1:"&COUNTA(DataResult))),MATCH(DataResult,DataResult,0),""),ROW()-ROW(DataResult)+1))),"",INDEX(DataResult,SMALL(IF(MATCH(DataResult,DataResult,0)=ROW(INDIRECT("1:"&COUNTA(DataResult))),MATCH(DataResult,DataResult,0),""),ROW()-ROW(DataResult)+1)))}
Regards,
Simon
{=IF(ISERR(INDEX(DataResult,SMALL(IF(MATCH(DataResult,DataResult,0)=ROW(IND IRECT("1:"&COUNTA(DataResult))),MATCH(DataResult,DataResult,0),""),ROW()-ROW(DataResult)+1))),"",INDEX(DataResult,SMALL(IF(MATCH(DataResult,DataResult,0)=ROW(INDIRECT("1:"&COUNTA(DataResult))),MATCH(DataResult,DataResult,0),""),ROW()-ROW(DataResult)+1)))}
Regards,
Simon