Select records that meet criteria in column E

[K. Georgiadis]

Hi folks,

I have a data list in A2:E250, each row representing a complete record of a customer and his projected sales. Each record is unique and the sales values are listed in column E.

I know how to extract the 1st, 2nd, 3rd, etc. sales value by using Excel's LARGE function, but what I want to do is to extract a complete record of a customer that meets set criteria for Column E. Say for example that I wanted to extract the complete record of a customer that had the 2nd largest sales in Column E. And what would happen if more than one customer happened to meet the criteria? Can VBA and, more fundamentally, Excel handle this or am I stepping into the domain of MS Access?

[Bob Phillips]

It depends upon what you mean by extract. It is easy to get the row number of the largest, 2nd, etc., elements, so you can use that to reference into each cell of the 'record', cell by cell.

[K. Georgiadis]

What I meant by "extract" was to dump the records that meet the criteria either on an empty range in the same worksheet or to a new worksheet. This would be similar to the workings of Advanced Filter (except that Advanced Filtering will not allow data to be 'dumped" on to a different worksheet from the one containing the data).

[geekgirlau]

What about something like this?

[K. Georgiadis]

There is a flaw: whether I enter 2, 3, or 4 in cell A3 of "Sales Data", I get $80,000 because there are three records that meet the criteria. However, by my definition entering 3 should return the record for Customer 14 with sales of $75,000 -- the 3rd highest sales volume.

[geekgirlau]

I've used the Excel "LARGE" function here, and unfortunately that's how it works. I'll have a bit of a play around and see what I can come up with, but it may require a mathematician's input here - definitely not my forte

[geekgirlau]

Okay, give this a try

[K. Georgiadis]

Where do I need to enter the rank criterion for the record extraction to take place?

Update: I got it! All I need to do is to make the datSales range dynamic, in case records are added later. Thanks!!

Update #2!! When I tried to make datSales dynamic with:

=OFFSET('Sales Data'!$A$6,0,0,COUNTA('Sales Data'!$A:$A,6)), running the macro returns an error on line:

Sheets("Sales Data").Show AllData

Why would that be?

[Bob Phillips]

Where do I need to enter the rank criterion for the record extraction to take place?

Update: I got it! All I need to do is to make the datSales range dynamic, in case records are added later. Thanks!!

Update #2!! When I tried to make datSales dynamic with:

=OFFSET('Sales Data'!$A$6,0,0,COUNTA('Sales Data'!$A:$A,6)), running the macro returns an error on line:

Sheets("Sales Data").Show AllData

Why would that be?

Because you have defined the name incorrectly as a single colum, so no filter is applied.

Use

=OFFSET('Sales Data'!$A$6,0,0,COUNTA('Sales Data'!$A:$A)-2,6)