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?

Assuming D2:D100 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


" wrote:

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?

I meant D2:D100 contain Type

" wrote:

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?

On 25 Feb, 20:14, Teethless mama
wrote:
I meant D2:D100 contain Type



" wrote:
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?- Hide quoted text -

- 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

On 25 Feb, 20:30, wrote:
On 25 Feb, 20:14, Teethless mama





wrote:
I meant D2:D100 contain Type

" wrote:
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?- Hide quoted text -

- 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- 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?

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))


" wrote:

On 25 Feb, 20:30, wrote:
On 25 Feb, 20:14, Teethless mama





wrote:
I meant D2:D100 contain Type

" wrote:
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?- Hide quoted text -

- 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- 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?

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)

wrote:
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?



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Try using AutoFilter

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

Biff

wrote in message
oups.com...
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?

What about possible ties?

Biff

"Teethless mama" wrote in message
...
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))


" wrote:

On 25 Feb, 20:30, wrote:
On 25 Feb, 20:14, Teethless mama





wrote:
I meant D2:D100 contain Type

" wrote:
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?- Hide
quoted text -

- 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- 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?