Hi XOR LX ,
I think I will try to explain , on David's behalf ; of course , this is only based on my understanding of his requirements , which may not be correct.
The way I have written the macro to satisfy his requirements , given two lists of numbers , which can be either simple one column multiple row ranges , or where the first list can be a derived array , the objective is to compare the two and return the number of matches , excluding duplicates.
To take an example , suppose the lists were :
4 ; 5 ; 8 ; 3 ; 8 ; 5 ; 9 ; 5
5 ; 8 ; 8 ; 6 ; 3 ; 3 ; 1 ; 1
Of course , the two example lists have the same number of elements , but this is not to be assumed i.e. the two lists can have a differing number of elements.
The above first list can be one of two :
1. A straightforward range A1 : A8
2. A derived array , using the formula :
CHOOSE({1,2,3,4,5,6,7,8},A1,A2,A3,A4,A5,A6,A7,B21)
or
CHOOSE({1;2;3;4;5;6;7;8},A1,A2,A3,A4,A5,A6,A7,B21)
Either way , what the function / formula should do is return the number of matches ; in this case , the matching numbers , excluding duplicates , would be 5 , 8 , 3 , and hence the number of matches would be 3.
Narayan