Display top 5 items from a list with matching criteria?

[Paul.Sutherby]

I have an excel sheet with several columns of data and would like to
pull a list of the top 10 records with matching criteria.

I have several columns like shown below (simplified for proof of
concept).

Record Number Record Name Record Value Type
1 One 100,000 B
2 Two 50,000 B
3 Three 80,000 A
4 Four 120,000 B
5 Five 100,000 A
etc...

I have a macro that sorts the column by record value, and want to
display the top 10 records of type B. With a vlookup I can pick out
the top item but I can not seem to get anything to then give me the
next matching record...

Is there anything I can do that will list the first matching record in
one cell, then give the next matching record in the next?

 

[Teethless mama]

Assuming D2100 contain Record Value, C2:C100 contain Record Value

=LARGE(IF($D$2:$D$100="B",$C$2:$C$100),ROWS($1:1))

ctrl+shift+enter, not just enter
Drag the Fill Handle as far as needed

 

[Teethless mama]

I meant D2100 contain Type

 

[Paul.Sutherby]

I meant D2100 contain Type









- Show quoted text -

That's great for showing the value... how do I need to tweak that
though to get it to show the text values too (ie the record name etc)?

Thanks

Paul

 

[Paul.Sutherby]

That's great for showing the value... how do I need to tweak that
though to get it to show the text values too (ie the record name etc)?

Thanks

Paul- Hide quoted text -

- Show quoted text -

I tried changing it to the following:

=TEXT(IF($D$2:$D$100="B",$C$2:$C$100),ROWS($1:1))

but whilst the first one works, all the other rows just show the first
value, not the 2nd, 3rd etc like the code you posted does (for numbers
only tho)

Any ideas what I need to do to get it to behave with text entries too?

 

[Teethless mama]

Assuming Record names in column B
I used the Column E as my previous reply

In F2: =INDEX($B$2:$B$100,MATCH(E2,$C$2:$C$100,0))

 

[Debra Dalgleish]

You could create a pivot table from the data, with record number, record
name and record value in the row area, and type in the data area.
Then, hide the subtotals, and set the Record Number to show the top 10
values.

Or, use an Advanced Filter to extract the records. In the criteria area,
use the Record Value column heading.
In the cell below, use the formula:
=">="&LARGE($C$2:$C$200,10)

 

[T. Valko]

Try using AutoFilter

Filter on Type = B
Then filter on Record Value = top 10

Biff

 

[T. Valko]

What about possible ties?

Biff

Assuming Record names in column B
I used the Column E as my previous reply

In F2: =INDEX($B$2:$B$100,MATCH(E2,$C$2:$C$100,0))