PDA

View Full Version : Ignoring item in unique list formula?



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

Bob Phillips
04-02-2007, 12:33 AM
=IF(ISERR(INDEX(DataResult,SMALL(IF((MATCH(DataResult,DataResult,0)=ROW(IND IRECT("1:"&COUNTA(DataResult))))*(DataResult<>"No Data"),MATCH(DataResult,DataResult,0),""),ROW()-ROW(DataResult)+1))),"",
INDEX(DataResult,SMALL(IF((MATCH(DataResult,DataResult,0)=ROW(INDIRECT("1:"&COUNTA(DataResult))))*(DataResult<>"No Data"),MATCH(DataResult,DataResult,0),""),ROW()-ROW(DataResult)+1)))

Simon Lloyd
04-02-2007, 01:36 AM
Bob as ever i'm indebted to you!, i did try this
(DataResult<>"No Data")and
(DataResult="No Data")but didn't use any operators like "*" i still havent grasped the use of that yet.

BTW did you get my e-mail? what do you think...interested?

Regards,
Simon

Bob Phillips
04-02-2007, 01:51 AM
It's an AND operator in this context Simon.

I'll reply to you on the email today.