+ Reply to Thread
Results 1 to 7 of 7

find highest number based on criteria and flag by X

  1. #1
    Forum Contributor
    Join Date
    12-23-2013
    Location
    Hungary
    MS-Off Ver
    Excel 2013
    Posts
    331

    find highest number based on criteria and flag by X

    Hello,

    I would need your support regarding a MAX value issue.

    I want to find the highest operation number based on order number and flag by X in the sample file. Could you please give me some advice how should I do that?
    Note: the original file has 100.000 row so the speed of formula is important.

    Thank you in advance any feedback!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: find highest number based on criteria and flag by X

    You could use a pivot table, like in the attached.
    Attached Files Attached Files
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Forum Contributor
    Join Date
    12-23-2013
    Location
    Hungary
    MS-Off Ver
    Excel 2013
    Posts
    331

    Re: find highest number based on criteria and flag by X

    Hello Bernie,

    Thank you very much the help, I did not think about pivot table!
    If I would like to use a formula, would you have an option?

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: find highest number based on criteria and flag by X

    Here's a formula solution:

    =IF(C5=MAXIFS($C$5:$C$19,$B$5:$B$19,B5),"x","")

    Extend the ranges to suit.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    Office 2016
    Posts
    2,807

    Re: find highest number based on criteria and flag by X

    This ARRAY FORMULA in D5 and down:
    =MAX(IF($B$5:$B$19=B5,$C$5:$C$19))

    In E5 and down:
    =IF(C5=D5,"X","")

    *You must press CTRL+SHIFT+ENTER to confirm entry of an ARRAY FORMULA in the Formula Bar.
    When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.


    BTW, @AliGW, the MAXIFS function is only available in Office 365.
    Last edited by leelnich; 06-14-2017 at 01:16 AM.

  6. #6
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: find highest number based on criteria and flag by X

    Well, since you have 100K rows and speed is important, and since MAXIFS is not available to you in XL2013, I would guess that the array formulas will be slower than using the pivot table - but try the array formula that leelnich posted - just do not use entire columns in the formula, which will make it even slower.

    If those are all too slow, then you might consider sorting your table, first by the order number, then by the amount DEscending. That way, your max value will be the first value when the ID changes, so you can use a formula like this in D2 (assuming that your headers are in row 1)

    =IF(B1<>B2,C2,D1)

    copied down. Then in E2, use

    =IF(D2=C2,"x","")

    and copy that down.

  7. #7
    Forum Contributor
    Join Date
    12-23-2013
    Location
    Hungary
    MS-Off Ver
    Excel 2013
    Posts
    331

    Re: find highest number based on criteria and flag by X

    Thank you very much for everybody!

+ 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. Replies: 1
    Last Post: 03-10-2017, 08:54 PM
  2. [SOLVED] Find highest version number for each material number
    By keld.strobel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-06-2015, 07:08 AM
  3. [SOLVED] How2 find number in range and return that number or if doesn't exist then the next highest
    By Crawfinator1 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-06-2014, 08:52 AM
  4. Find highest number of sales in a date range and show seller and sale number
    By audiofreak in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-06-2012, 03:34 AM
  5. Replies: 3
    Last Post: 07-03-2012, 09:09 PM
  6. 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
  7. Find highest based on criteria
    By timtrag12 in forum Excel General
    Replies: 2
    Last Post: 03-27-2011, 05:42 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