My array formula TOP items

[b_rianv]

Ok guys last one.

Here is my array formula which gives me the TOP item found in a list. It only gives me 1, I want top 5 items.

Please Login or Register  to view this content.

Is there a formula that will give me the TOP 5 items?

Thank you,
Brian

[Tony Valko]

What kind of data is it? Text? Numbers?

[b_rianv]

It is for "text" Tony.

[Tony Valko]

I have the data in column A sorted just to make it easier to see what results should be expected.

Data Range

	A	B	C
1	Climp	------	Climp
2	Climp		Rosk
3	Climp		Plump
4	Rosk		Jumor
5	Rosk		Puss
6	Rosk
7	Plump
8	Plump
9	Jumor
10	Jumor
11	Puss
12	Puss
13	Blim
14	Blam
15	Toots

This array formula** entered in C1:

=INDEX(A1:A15,MODE(MATCH(A1:A15,A1:A15,0)))

This array formula** entered in C2 and copied down to C5:

=INDEX(A$1:A$15,MODE(IF(COUNTIF(C$1:C1,A$1:A$15)=0,MATCH(A$1:A$15,A$1:A$15,0))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[b_rianv]

Thank you Tony very much.

[b_rianv]

This was a BIG help!

[Tony Valko]

You're welcome. Thanks for the feedback!

[b_rianv]

Somethings not right...I keep getting #N/A

[b_rianv]

I don't think MODE is working for this, I am tracing the step and it keeps pointing to MODE

[b_rianv]

Maybe it should be Frequency,instead of MODE for the first part of the formula

[b_rianv]

The problem is this...the formulas have to account for empty cells that contain no data yet,as these cells will be populated with data as the weeks go by. That's why I am getting the #N/A.
The column range is much greater the 15 my friend. The actual range for the column is B5:B304. Which as the weeks go by data is inputted in to the cells.

Can that be fixed?

[XOR LX]

This array formula** entered in C1:

=INDEX(A1:A15,MODE(MATCH(A1:A15,A1:A15,0)))

Actually that one doesn't require CSE.

Regards

[XOR LX]

To amend Tony's set-up to account for blanks:

In C1, array formula**:

=INDEX(A1:A15,MODE(IF(A1:A15<>"",MATCH(A1:A15,A1:A15,0))))

In C2, array formula**:

=INDEX(A$1:A$15,MODE(IF(A$1:A$15<>"",IF(COUNTIF(C$1:C1,A$1:A$15)=0,MATCH(A$1:A$15,A$1:A$15,0)))))

and copy down to C5.

I notice you don't say what should be the result if the number of entries with a frequency of 2 or more is less than 5?

Regards

[Tony Valko]

Actually that one doesn't require CSE.

Yes, I see that!

[Tony Valko]

Can that be fixed?

Yes, I think using a dynamic range is in order.

Create this named range...
Name: Range (or whatever you want to call it)
Refers to: =$B$5:INDEX($B:$B,MATCH("zzzzz",$B:$B))

Then, the formulas would be:

Entered in C1:

=INDEX(Range,MODE(MATCH(Range,Range,0)))

This array formula** entered in C2 and copied down to C5:

=INDEX(Range,MODE(IF(COUNTIF(C$1:C1,Range)=0,MATCH(Range,Range,0))))

[XOR LX]

Ah, yes. Perhaps I misunderstood. If the blanks are only at the very end of the data then Tony's amended set-up will be much more efficient than that I gave.

Regards

[b_rianv]

Yes it is fixed now by both formulas, Thank you both for your time and effort to help me.

Have a great weekend guys!!

[Tony Valko]

You're welcome. Thanks for the feedback!