+ Reply to Thread
Results 1 to 5 of 5

Extract rows from an array by highest value in Column A for a unique Column B

  1. #1
    Registered User
    Join Date
    12-09-2022
    Location
    New York
    MS-Off Ver
    365
    Posts
    2

    Extract rows from an array by highest value in Column A for a unique Column B

    Hello good people,

    I have an array created by some elaborate filtering in formulas, see below (this is a dynamic list, not an actual table that can be referenced as such).
    My goal is to filter this array so that for each user I see the row containing the highest value in the first column.

    Screen Shot 2022-12-09 at 3.58.45 PM.png

    The expected output would look something like this:

    Screen Shot 2022-12-09 at 3.53.07 PM.png

    This must be done in a formula with no power queries and pivot tables etc because (1) Excel for Mac and (2) I got here with formulas so I might as well complete it with formulas.

    I'm stuck! Tried all the INDEX / MATCH / MAXIF / XLOOKUP etc combinations I can think of.

    Any advice?

    Thank you!

  2. #2
    Registered User
    Join Date
    06-02-2020
    Location
    Turkey
    MS-Off Ver
    365 TR - V.2309
    Posts
    97

    Re: Extract rows from an array by highest value in Column A for a unique Column B

    Hi,

    According to my interpretation from the picture..(I may be misinterpreting)

    (Elaborated an array with dynamic array formulas in cell M3)

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Or

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    3,030

    Re: Extract rows from an array by highest value in Column A for a unique Column B

    Please read the yellow banner at the top of the page and attach a good sample.

  4. #4
    Registered User
    Join Date
    12-09-2022
    Location
    New York
    MS-Off Ver
    365
    Posts
    2

    Re: Extract rows from an array by highest value in Column A for a unique Column B

    Apologies Gregb11, I will do it next time.

    52779 your first solution worked exactly as needed! Thank you. For some reason there is very little documentation out there to make this calculation based on an array and not a table or set values, so I hope this thread will come up in future searches.

    Thank you!

  5. #5
    Registered User
    Join Date
    06-02-2020
    Location
    Turkey
    MS-Off Ver
    365 TR - V.2309
    Posts
    97

    Re: Extract rows from an array by highest value in Column A for a unique Column B

    @talazar

    Glad to have helped.
    Thanks the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Non Array Formula to Extract Unique Values in column A2:A61 and Place in B2:B61
    By bjnockle in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-10-2021, 01:25 PM
  2. [SOLVED] Non Array Formula to Extract Unique Values into Column B
    By bjnockle in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-11-2021, 07:34 PM
  3. Extract unique items from array and place in a column.
    By Chrono9084 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-17-2018, 11:48 AM
  4. How Do You Count Unique Cell Entries In A Column With Many Rows Without Using Table Array
    By OffInParametricLand in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2014, 04:20 PM
  5. [SOLVED] Find highest value in Column V for unique value in column R
    By rbac in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-06-2013, 01:12 PM
  6. Replies: 8
    Last Post: 08-20-2012, 01:27 PM
  7. Replies: 3
    Last Post: 08-10-2006, 11:40 PM

Tags for this Thread

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