+ Reply to Thread
Results 1 to 2 of 2

Ignore Duplicates, designate best value

  1. #1
    Registered User
    Join Date
    07-19-2016
    Location
    Baton Rouge, Louisiana
    MS-Off Ver
    2016
    Posts
    41

    Ignore Duplicates, designate best value

    Hi all,

    I am trying to create a table column that will designate when an athlete achieves a Personal Record (PR) on a particular test. Within my list, there are numbers where higher values are better (longer/higher distances, faster speeds etc...) and values that are better when they are lower (sprint times). My current formula is as follows:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In short, the first part of the formula is saying that if the test matches any of the listed test names, to do a Max If (I had to build it because although I have Office 2016, I believe there may be an issue with my licensing that doesn't give me the actual maxifs formula). The second part says that if it doesn't equal any of those test names, do a Min If formula.

    My question is how can I alter this formula to only give an athlete a PR the first time they accomplish it, and then to leave the PR designation column blank should they match that value anytime after that? I believe a sumproduct formula integrated within that formula should work, but all ideas welcome.

    I have attached my workbook, and filtered it down to an example athlete who had several values that were PRs, but I would only like for the first time he achieved 32 on the max output test to be designated as a PR, and the subsequent tests to just show as blanks.
    Attached Files Attached Files
    Last edited by CoachK88; 12-11-2019 at 05:11 PM. Reason: File Added

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Ignore Duplicates, designate best value

    Well... I would do it rather differently.

    Firstly your sample was FAR too big to facilitate manual checking, so I cut it back a lot.

    I set up a Table (Events) to specifying whether you want a MAX or a MIN. This formula gives a list of unique events:
    =IFERROR(INDEX(Table8[Test],MATCH(0,INDEX(COUNTIF($A$1:$A1,Table8[Test]),0),0)),"")

    I then manually assigned MAX or MIN to these. Mostly the choices were random!! Please check & correct.

    I then this formula to return the desired result:

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


    You can delete columns D, E, F & I.

    The results will look a bit odd until you correct the MAX/MINS !!!
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

+ 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. Look up multiple results but ignore duplicates
    By aaronfi in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-21-2015, 09:51 AM
  2. [SOLVED] Ignore duplicates in list
    By cmb80 in forum Excel General
    Replies: 9
    Last Post: 01-20-2015, 11:54 AM
  3. Adjust formula to ignore duplicates
    By dasseya1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2013, 03:41 PM
  4. Replies: 12
    Last Post: 07-04-2013, 07:26 AM
  5. Copy Rows - Ignore duplicates
    By revenge4ash89 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-17-2012, 06:12 PM
  6. Ignore Duplicates in Counts
    By dfq in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-15-2007, 03:11 AM
  7. Ignore Duplicates
    By felicsx in forum Excel General
    Replies: 0
    Last Post: 08-23-2006, 03:55 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