Top 5 from sub-array

M

morsagmon

New Member
Joined
May 6, 2015
Messages
28
Hi.

I have a table (PipelineTable) with these columns:
P
Amount

I need to extract the top 5 amounts from the rows that meet a criteria: P is a fraction (between 0 and 1).
For example, if this is the PipelineTable:

[TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD]P[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]0.5[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]0.75[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]0.5[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]0.75[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]0.25[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]0.75[/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]
















The top 5 results I'm after would be:
45
25
25
20
12

I'd prefer not to code a script for this and use a direct formula.
Using Array Formulas is fine if needed.

Thanks!
Mor
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid"]
<tbody>[TR]
[TD]
1​
[/TD]
[TD]P[/TD]
[TD]Amount[/TD]
[TD]Top 5[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]
0.5​
[/TD]
[TD]
25​
[/TD]
[TD]
45​
[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]
0​
[/TD]
[TD]
50​
[/TD]
[TD]
25​
[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]
0.75​
[/TD]
[TD]
45​
[/TD]
[TD]
25​
[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]
1​
[/TD]
[TD]
12​
[/TD]
[TD]
20​
[/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]
0.5​
[/TD]
[TD]
20​
[/TD]
[TD]
12​
[/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD]
0.75​
[/TD]
[TD]
25​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
8​
[/TD]
[TD]
0.25​
[/TD]
[TD]
8​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
9​
[/TD]
[TD]
1​
[/TD]
[TD]
30​
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
10​
[/TD]
[TD]
0.75​
[/TD]
[TD]
12​
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]
C​
[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]
=LARGE(IF($A$2:$A$10<>INT($A$2:$A$10),IF(LARGE($A$2:$A$10,ROW(A1)),$B$2:$B$10)),ROW(A1))​
[/TD]
[/TR]
</tbody>[/TABLE]


Ctrl+Shift+Enter
 
Upvote 0
krykm said:
[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]
C​
[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]
=LARGE(IF($A$2:$A$10<>INT($A$2:$A$10),IF(LARGE($A$2:$A$10,ROW(A1)),$B$2:$B$10)),ROW(A1))​
[/TD]
[/TR]
</tbody>[/TABLE]


Ctrl+Shift+Enter
Click to expand...

Thanks! works great for the Amount column. However, trying to replicate for other columns in the table, either don't fetch the correct values (for numerical columns) or doesn't work at all (for text columns).
How can we make that more generic for any column I want to fetch (still based on the same P criteria and rank of amount)?

Mor
 
Upvote 0
=LARGE(IF($A$2:$A$10<1,IF($A$2:$A$10>0,$B$2:$B$10)),ROW(A1)) Ctrl+Shift+Enter
=LARGE(IF("here your criteria","here the range you want to see tops"),"ROW(A1)")
ROW(A1)=1 when you drag it down then will be ROW(A2)=2 and so on. It means first large number, then for second row second large number and so on.

I didn't quite understand about the text. If still doesn't work, would you mind putting an example with texts?
 
Upvote 0
Thanks, krykm.

I figured out the logic of your formula. Here's what I'm aiming for:


[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD]P
[/TD]
[TD]Amount
[/TD]
[TD]Description
[/TD]
[TD]ID
[/TD]
[/TR]
[TR]
[TD]0.5
[/TD]
[TD]12
[/TD]
[TD]Third Record
[/TD]
[TD]00548
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]25
[/TD]
[TD]Not selected
[/TD]
[TD]10155
[/TD]
[/TR]
[TR]
[TD]0.25
[/TD]
[TD]20
[/TD]
[TD]First record
[/TD]
[TD]05684
[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD]18
[/TD]
[TD]Not Selected
[/TD]
[TD]00048
[/TD]
[/TR]
[TR]
[TD]0.75
[/TD]
[TD]15
[/TD]
[TD]Second record
[/TD]
[TD]60588
[/TD]
[/TR]
[TR]
[TD]0.5
[/TD]
[TD]12
[/TD]
[TD]Fourth Record
[/TD]
[TD]55001
[/TD]
[/TR]
[TR]
[TD]0.5
[/TD]
[TD]10
[/TD]
[TD]Fifth Record
[/TD]
[TD]01015
[/TD]
[/TR]
</tbody>[/TABLE]













The resulting table will look like this:

[TABLE="class: grid, width: 250, align: left"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Description
[/TD]
[TD]Amount
[/TD]
[/TR]
[TR]
[TD]05684
[/TD]
[TD]First record
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]60588
[/TD]
[TD]Second record
[/TD]
[TD]15
[/TD]
[/TR]
[TR]
[TD]00548
[/TD]
[TD]Third Record
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]55001
[/TD]
[TD]Fourth Record
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]01015
[/TD]
[TD]Fifth Record
[/TD]
[TD]10
[/TD]
[/TR]
</tbody>[/TABLE]











(I have another column tot include, probably not necessary here for the explanation).

I don't have a unique ID column and I don't want to add a helper column to that table. Need to identify records handle by row number probably...

Thanks!
Mor
 
Upvote 0
[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]
A​
[/TD]
[TD]
B​
[/TD]
[TD]
C​
[/TD]
[TD]
D​
[/TD]
[TD]
E​
[/TD]
[TD]
F​
[/TD]
[TD]
G​
[/TD]
[TD]
H​
[/TD]
[/TR]
[TR]
[TD]
1​
[/TD]
[TD]P[/TD]
[TD]Amount[/TD]
[TD]Description[/TD]
[TD]ID[/TD]
[TD][/TD]
[TD]ID[/TD]
[TD]Description[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]0.5[/TD]
[TD]
12​
[/TD]
[TD]Third Record[/TD]
[TD]
548​
[/TD]
[TD][/TD]
[TD]5684[/TD]
[TD]First record[/TD]
[TD]
20​
[/TD]
[/TR]
[TR]
[TD]
3​
[/TD]
[TD]1[/TD]
[TD]
25​
[/TD]
[TD]Not selected[/TD]
[TD]
10155​
[/TD]
[TD][/TD]
[TD]60588[/TD]
[TD]Second record[/TD]
[TD]
15​
[/TD]
[/TR]
[TR]
[TD]
4​
[/TD]
[TD]0.3[/TD]
[TD]
20​
[/TD]
[TD]First record[/TD]
[TD]
5684​
[/TD]
[TD][/TD]
[TD]548[/TD]
[TD]Third Record[/TD]
[TD]
12​
[/TD]
[/TR]
[TR]
[TD]
5​
[/TD]
[TD]0[/TD]
[TD]
18​
[/TD]
[TD]Not Selected[/TD]
[TD]
48​
[/TD]
[TD][/TD]
[TD]55001[/TD]
[TD]Fourth Record[/TD]
[TD]
12​
[/TD]
[/TR]
[TR]
[TD]
6​
[/TD]
[TD]0.8[/TD]
[TD]
15​
[/TD]
[TD]Second record[/TD]
[TD]
60588​
[/TD]
[TD][/TD]
[TD]1015[/TD]
[TD]Fifth Record[/TD]
[TD]
10​
[/TD]
[/TR]
[TR]
[TD]
7​
[/TD]
[TD]0.5[/TD]
[TD]
12​
[/TD]
[TD]Fourth Record[/TD]
[TD]
55001​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
8​
[/TD]
[TD]0.5[/TD]
[TD]
10​
[/TD]
[TD]Fifth Record[/TD]
[TD]
1015​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]
H​
[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]
=LARGE(IF($A$2:$A$8>0,IF($A$2:$A$8<1,$B$2:$B$8)),ROW(A1))​
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]
G​
[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]=INDEX($C$2:$C$8,SMALL(IF($B$2:$B$8=H2,ROW($C$2:$C$8)-ROW($C$2)+1),COUNTIF(H2:$H$2,H2)))[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]
F​
[/TD]
[/TR]
[TR]
[TD]
2​
[/TD]
[TD]=INDEX($D$2:$D$8,MATCH(H2&G2,$B$2:$B$8&$C$2:$C$8,0))[/TD]
[/TR]
</tbody>[/TABLE]


First put the formula to H2, drag down. Then G2, drag down. Then F2, drag down.

For each of them Ctrl+Shift+Enter

Hope helps this time
 
Upvote 0
Assumed your table in ranges A1:D8 (Including header:

For ID :

=IF(ROWS($A$1:A1)<=5,INDEX($D$2:$D$8,MATCH(LARGE(IF(($B$2:$B$8>0)*($C$2:$C$8<>"Not selected"),$B$2:$B$8+ROW($B$2:$B$8)/1000000,0),ROWS($A$1:A1)),IF($B$2:$B$8>0,$B$2:$B$8+ROW($B$2:$B$8)/1000000,0),0),0),"")

For Description :

=IF(ROWS($A$1:A1)<=5,INDEX($C$2:$C$8,MATCH(LARGE(IF(($B$2:$B$8>0)*($C$2:$C$8<>"Not selected"),$B$2:$B$8+ROW($B$2:$B$8)/1000000,0),ROWS($A$1:A1)),IF($B$2:$B$8>0,$B$2:$B$8+ROW($B$2:$B$8)/1000000,0),0),0),"")

For Amount :

=IF(ROWS($A$1:A1)<=5,INDEX($B$2:$B$8,MATCH(LARGE(IF(($B$2:$B$8>0)*($C$2:$C$8<>"Not selected"),$B$2:$B$8+ROW($B$2:$B$8)/1000000,0),ROWS($A$1:A1)),IF($B$2:$B$8>0,$B$2:$B$8+ROW($B$2:$B$8)/1000000,0),0),0),"")

Array Entered.........
 
Upvote 0
Yesssss,
Thanks, krykm, works!
I had to fix a little something in the second formula (no need to add 1 to the headers row).
 
Upvote 0
Thank you, azumi, for your offer.

You missed my initial post up the thread, so your formulas are not aiming at what I need. The description is not the criteria. krykm's solution works in my environment now.
Thanks!
Mor
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Need formula to consolidate data
Replies
1
Views
171
Fluff
Fluff
M
  • Question Question
select value two columns to the left of variable in an array
Replies
1
Views
270
myall_blues
myall_blues
tabbytomo
  • Question Question
=IF Exact formula stops working part way through
Replies
1
Views
237
arthurbr
arthurbr
Z
  • Question Question
VBA: Pass Args back to Sub from PVT Sub
Replies
4
Views
161
Skyybot
Skyybot
S
  • Solved
Ranking Array need to move as per the data
2
Replies
16
Views
465
sksanjeev786
S

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top