create an array with unique items IN MEMORY

  • Thread starter Thread starter Werner Rohrmoser
  • Start date Start date
W

Werner Rohrmoser

Hello,

in the usegroup there are many postings describing how to get
an array of unique items listed on a spreadsheet.

I have the following formula:
{=SUM(--(SUMIF(Months,{"Jan","Feb","Mar"},Values)>0))}

I'd like to replace the array constant "{"Jan","Feb","Mar"}"
by an formula which creates an array of unique items from
a range which I've called "Months".

Regards
Werner
 
1. If you have the latest version of Longre's free morefunc.xll add-in...

=COUNTDIFF(IF(Values>0,Months,0),FALSE,0)

which must be confirmed with control+shift+enter.

2. Otherwise...

=SUM(IF(FREQUENCY(IF((Values>0)*(Months<>""),MATCH(Months,Months,0)),ROW(Months))>0,1))

which also needs to be confirmed with control+shift+enter.
 
Back
Top