Large function

[Pavan Kumar Sistla]

Dear All,

Could you please let me know the formula to ignore duplicates in a large function.

Eg: I have data like: 40, 50, 60, 40, 50, 30 etc. I want top 5 values by ignoring duplicates.

Thanks,
Pavan.

 

[PCosta87]

Hi,

I'm sure someone can figure out a way of doing it in a single function, but I think I can get you half way there... in a column have the LARGE function and in another the unique entries (see attached).

Hope this helps a bit.

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[bosco_yip]

Hi,

I'm sure someone can figure out a way of doing it in a single function, but I think I can get you half way there... in a column have the LARGE function and in another the unique entries (see attached).

Hope this helps a bit.

Hi Pcosta,

Using your layout, another option without helper.

In D2, non-CSE formula copy down :

=IF(MIN($A$2:$A$12)=MIN(D$1:D1),"",LARGE($A$2:$A$12,COUNTIF($A$2:$A$12,">="&D1)+1))

Regards
Bosco

 

[Pavan Kumar Sistla]

Thank you Bosco, Pcosta and Narayan. However, I need top 5 values only instead of all unique values.

Eg:-
A - 10
B - 50
C - 30
D - 45
E - 33
F - 50
G - 30
H - 10
I - 70
J - 80
K - 70

I want top 5 values along with A, B, C etc. Please let me know. Regards, Pavan.

 

[Asheesh]

Thank you Bosco, Pcosta and Narayan. However, I need top 5 values only instead of all unique values.

Eg:-
A - 10
B - 50
C - 30
D - 45
E - 33
F - 50
G - 30
H - 10
I - 70
J - 80
K - 70

I want top 5 values along with A, B, C etc. Please let me know. Regards, Pavan.

Do you mean first 5 values, in the above case the formula should return 10,50,30,45,33 please correct..

Suppose your list was like
Eg 2:-
A - 10
B - 30
C - 30
D - 45
E - 33
F - 50
G - 30
H - 10
I - 70
J - 80
K - 70

in eg 2, the formula should return: 10, 30, 45,33,50

Please confirm

 

[Pavan Kumar Sistla]

No I need the top 5 values along with the alphabet

 

[Asheesh]

Per given example 50 is repeated twice so which letter should the formula pick..