Get Rows with highest value in a column

Hi

I have a spreadsheet with events data. I would like to get rows with the highest value of Business Criticality.

If there are more than one rows with hightest values i would like to get all those rows and copy into a new sheet in same workbook. I would like to get header rows in the new sheet as well.

Can some one please help with VBA for this?

Pls see attached sample data sheet. For example i would like to copy the three highlighted rows to a new sheet [As they are with highest business criticality 10 in the column]

If i had only three rows with criticality as 9, 10 and 11 then i would like pull out only one row with value 11 as that's the highest one.

I need to do a few more opeartions so yes really need VBA. I am trying to get a few things with a click of a button so really need VBA.

Just to show you how easy it is using formulae, I have set this up for you in the attached file using three formulae. In cell O2 of Sheet1 I have used this formula:

=IF(N2=MAX(N:N),MAX(O$1:O1)+1,"-")

which can be copied down to the bottom of your data or beyond to accommodate new data being added (I've copied to row 25 in the attached file, as can be seen with the hyphens). This formula identifies the records which match the criteria and allocates a unique sequential number to each one. I've put the following formula in cell O2 of Sheet2:

=IFERROR(MATCH(ROWS($1:1),Sheet1!O:O,0),"-")

which returns the row number of the first matching record, or "-" if there is no matching record. When copied down, this will look for the second, then the third matching record, and so on. You can then use this formula in cell A2:

=IF(OR($O2="",$O2="-"),"",INDEX(Sheet1!A:A,$O2))

which can be copied across to N2, and this will return the data from the appropriate columns of Sheet1 for the first matching record. The formulae in row 2 can then be copied down as far as you need them (I've copied to row 10) to accommodate the amount of data that you expect.

This is now fully automatic - if you change one of those values of 10 in Sheet1 to 11, then the records displayed in Sheet2 will automatically adjust - no need to click a button.

As Ford indicated, it is possible to do all this with one array formula in Sheet2, but I prefer this approach as it is quicker to execute and uses simpler formulae.

Hope this helps.

Pete

It takes a bit longer to describe, though !!

Pete

Yes, I noticed that you didn't try to explain what it was doing - another reason for the OP to avoid it if it is hard to understand the different elements.

Thanks for the rep, by the way.

Pete

Really appreciate your help guys.
Don't get me wrong, I have to do this using VBA. I know but that's the requirement I am working with.

Any chance of doing this using VBA please?

Any help here please ?