Copying rows that meet two criteria to another worksheet

I am trying to copy entire rows from the first worksheet in a document to another worksheet.

The desired layout on the second worksheet is that the lowest values from Column A within each of four (4) criteria in Column L are rank ordered (thus the lowest values).

Column A - rank order priority of the overall spreadsheet (based on criteria summed from other columns) (It is a list of projects placed in priority for funding.)
Column L - there are four agencies (The four agencies are competing on funding based on the prioritization in Column A.)

I would like for the "top 5" rows for each of the four agencies listed on the second sheet. The only "requirement" is that the data must be sorted by each agency.

Any help?

If you throw me a sample sheet, I could throw you a collection of formulas using CountIf, Sumproduct, Index, and Match.

Another option might be to use a macro in conjunction with advanced filter to copy the data you wanted with just a click.

Let me "dumb down" our chart - sensitive info . . . I'll post in about ten minutes as an attachment.

Thanks!

No problem.

Here are two examples I've put together for others. It will likely use the same kind of logic.

AutoSort has a four way breakdown, showing all items.

The tutorial I wrote shows a three way, with description of code.

For you, I'd just show 5 returns per item, instead of all.

Attachment 261445

Attachment 261446

Okay, the attachment will work for what I think I need!

The information should come from the Tab labeled "WOPL" and formatted/copied into Tab labeled "Top 5 By Division".

I'll keep watching here . . .

I love a good challenge.

Sounds great!! It is a challenge well beyond my limited knowledge of EXCEL!!!

Thanks for the help in advance!!!

I'm at a boring conference. This was way more interesting.

I've got it almost completely done, just working on preventing errors where there's less than 5 records to return.

Otherwise, done.

Attachment 261453

This looks sweet! My buddy and I are struggling with several other things, but this is killing us!!

Thanks! Looking forward to the final product!

And here's a revised version.

I updated the formula to leave blanks instead of zeroes for the comments field and others.

Also created a match formula that will find top five without errors, even if there's only one or two records.

Attachment 261458

Did you need me to adapt this to fit more information?

I can revise the formulas to accommodate more rows entered on the first page if you need.

Nope! Looks great!!! And fills the need perfectly for what we are doing!

Just know that you are a star in this office right now!!

We owe you a beer if you ever get to Colorado Springs . . . Hell, we owe you a case!

Thanks!