Logic of the formula

[sambit]

Sir,
I have a Data List in Coloum B and put the formula for getting Unique value which is reflect in Coloum E.

I used the Array formula for getting unique value i.e=INDEX($B$2:$B$13,MATCH(0,COUNTIF($B$2:$B$13,"<"&$B$2:$B$13)-SUM(COUNTIF($B$2:$B$13,"="&E$1:E1)),0))

can anybody describe the logic of the below formula.

COUNTIF($B$2:$B$13,"<"&$B$2:$B$13) ------>

SUM(COUNTIF($B$2:$B$13,"="&E$1:E1) ------>

Colum B Coloum E

Row 1 Data List Unique Value
2 A A
3 C B
4 C C
5 A
6 B
7 B
8 C
9 B
10 B
11 B
12 C
13 C

 

[Somendra Misra]

Hi Sambit,

The above formula is not only extracting unique value but also giving the sorted output.

Basically, this part COUNTIF($B$2:$B$13,"<"&$B$2:$B$13) of the formula is giving an array of the size of number of elements in the list which are greater then themselves, So say list is {A;B;A;C;D} so this part will give an array {0;2;0;3;4} as there is no one grater than A so for A you get 0 in both position. Like wise Greater than B there are two A so you get 2 in position of B. And like that.

Now the next part will come to exclude the element in Unique sorted list which appeared as result , so this part is handled by SUM(COUNTIF($B$2:$B$13,"="&E$1:E1) . I assume your formula strats from E2 and copied down, so this will count the result in the list and add them UP. So if you enter this in E2 and press enter you will get 0, as the header in E1 is not in the list , than finally you subrtract 0 from the COUNTIF array so the final lookup array for MATCH is {0;2;0;3;4}. So you get A as a result.

Now when you copy this down than SUM will get an array of {0;2} because header is not there in the list and A is twice in the list. So now lookup array is like {-2;0;-2;1;2}. So when MATCHED it will give you B as result and the same will continue.

I hope this will clear something out.

Regards,

 

[sambit]

Somendra Sir,
Thanks you for clear the array logic.