+ Reply to Thread
Results 1 to 2 of 2

Match/Large with Duplicates (I want to include the duplicates)

  1. #1
    Registered User
    Join Date
    08-12-2013
    Location
    New Jersey
    MS-Off Ver
    Excel 2003
    Posts
    4

    Match/Large with Duplicates (I want to include the duplicates)

    Hi Everyone.

    I know the question of Match & Large has been answered before, I have seen the threads, but most seem to want to ignore the duplicates, whereas I want to be able to return the position (column) of these data points. The data is organized with dates in column A and then 29 data points across columns B to AD. For example:

    1/1/2000 .01 .02 .03 .04 .05 .06 .06 .07 .05 .04, etc

    I want to be able to find the 1st, 2nd, and 3rd largest data points and then return their column numbers. If I use Match & Large to find these points on the above data, I can only get it to return column#9 for largest, and then column#7 for both the 2nd and 3rd largest. I want it to return column#9, column#7, and column#8, for first, second & third.

    Can anyone help?

    thank you.

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Match/Large with Duplicates (I want to include the duplicates)

    The only tie breaker formula I know of offhand would be to use

    =rank(A2,$A$2:$L$2,1)+countif($A$2:A2,A2)-1

    to establish values, and then use Match.

    match-rank.xlsx
    Last edited by daffodil11; 08-13-2013 at 01:19 PM. Reason: Absolute references; Reverse Rank

+ 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] Problem Using LARGE formula with Index/Match to pull values when there are Duplicates
    By BDavis in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-11-2014, 02:23 PM
  2. Edit existing VBA to remove duplicates and include conditional?
    By MargyHall in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-26-2013, 06:07 AM
  3. [SOLVED] Identify Duplicates and Non-Duplicates within an Array (Match)
    By simpson11 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-26-2013, 09:49 AM
  4. using large function with duplicates
    By merlyn45 in forum Excel General
    Replies: 2
    Last Post: 10-10-2012, 05:30 PM
  5. Replies: 3
    Last Post: 03-09-2011, 07:00 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