Results 1 to 11 of 11

Need to select earliest five rows matching highest value, and next highest if not enough

Threaded View

Ochimus Need to select earliest five... 05-04-2016, 09:25 AM
JOHN H. DAVIS Re: Need to select earliest... 05-04-2016, 09:57 AM
Ochimus Re: Need to select earliest... 05-04-2016, 11:01 AM
JOHN H. DAVIS Re: Need to select earliest... 05-04-2016, 11:06 AM
CAntosh Re: Need to select earliest... 05-04-2016, 11:45 AM
Ochimus Re: Need to select earliest... 05-04-2016, 02:32 PM
JOHN H. DAVIS Re: Need to select earliest... 05-04-2016, 03:08 PM
CAntosh Re: Need to select earliest... 05-04-2016, 03:58 PM
Ochimus Re: Need to select earliest... 05-04-2016, 06:39 PM
CAntosh Re: Need to select earliest... 05-05-2016, 10:02 AM
Ochimus Re: Need to select earliest... 05-05-2016, 11:26 AM
  1. #1
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365 Version 2501 64-bit
    Posts
    1,491

    Need to select earliest five rows matching highest value, and next highest if not enough

    Hopefully the attachment will make everything clear.

    Need to select five entries that were the "first to score" values in a range.

    As shown on the attachment, it is straightforward if five or more entries achieve the Highest Score, Code needs to select the first five in the bloc, and copy and paste to a different location.

    But if there are less than five, code should copy and paste however many there are, then find the bloc with the Second Highest scorers.

    If there are more than the balance needed, copy however many are required, starting with the first match, and copy and paste them.

    If there are less than the balance, copy and paste what there is, and look for Third Highest number. Start with the first match, and copy and paste them.

    Initial thought was to use a formula in Col C to establish how many entries there are for each value, (=COUNTIF(B$1:B$21,B2) copied down),

    Starting at row 21 and working upward, the Code could then use that as the "offset", to measure whether each value has more than the number of rows required?
    And if there are more than five rows, it copies from the Offset cell down to the Offset,offset(5,0)

        Range("B21").Select
        a = ActiveCell
        ActiveCell.Offset(ActiveCell.FormulaR1C1 = "=COUNTIF(R1C[-1]:R21C[-1],RC[-1])").Select
        If ActiveCell.Offset(ActiveCell.FormulaR1C1 = "=COUNTIF(R1C[-2]:R51C[-2],RC[-2])") >= ActiveCell.Offset(-5, 0) Then
        Range ActiveCell.Offset(ActiveCell.FormulaR1C1 = "=COUNTIF(R1C[-1]:R21C[-1],RC[-1])"),range(Range ActiveCell.Offset(ActiveCell.FormulaR1C1 = "=COUNTIF(R1C[-1]:R21C[-1],RC[-1])").Offset(5,0).select
    But the "options" I would have to build in suggest I am missing something much simpler.

    Any suggestions and pokinters received gratefully as ever.

    Ochimus
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Max formula to return total of highest, second highest and third highest value
    By JonWilf in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-06-2016, 08:20 AM
  2. Find highest value in table and return earliest occurence (name)
    By potatoman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-10-2014, 02:44 PM
  3. Macro to select rows with respect to highest value in column
    By gokzee in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-05-2012, 09:41 PM
  4. 2 columns of data matching highest with highest
    By ronaldchristie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-03-2012, 07:57 AM
  5. VBA code to find highest and 2nd highest number based in criteria
    By Michael007 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-25-2011, 08:38 AM
  6. Replies: 2
    Last Post: 09-19-2008, 10:22 AM
  7. Replies: 3
    Last Post: 08-10-2006, 11:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1