+ Reply to Thread
Results 1 to 3 of 3

Ranking list with duplicate values

  1. #1
    Registered User
    Join Date
    02-26-2008
    MS-Off Ver
    Excel 2016
    Posts
    23

    Ranking list with duplicate values

    Hello,
    My company receives orders twice a week. In column A, I have a list of event times. In column B, I have a list of when the shipment arrives for the row's event time. In column C, I would like the rank of the delivery date in ascending order. In my attached spreadsheet Column D shows my desired answer.

    I am trying to work in a table, so it would be helpful to use the table's structured referencing.

    What causes me trouble is that each delivery occurs multiple times in the column, and I want the next delivery to have a rank of 2.
    Also, If the event is in the past, I have the delivery left blank.

    Delivery___,Rnk,I don't want
    Wed 4:00 PM, 1 ,1
    Wed 4:00 PM, 1 ,1
    Wed 4:00 PM, 1 ,1
    Wed 4:00 PM, 1 ,1
    Sat 4:00 PM, 2 ,5
    Sat 4:00 PM, 2 ,5
    Sat 4:00 PM, 2 ,5
    Sat 4:00 PM, 2 ,5
    Sat 4:00 PM, 2 ,5

    Thank you for the help!!
    Attached Files Attached Files

  2. #2
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Ranking list with duplicate values

    Try this in C5

    =IF([@[Delivery Date]]="","",IF([@[Delivery Date]]=B4,C4,SUM(C4,1)))
    Last edited by Cutter; 06-21-2012 at 12:24 PM.

  3. #3
    Registered User
    Join Date
    02-26-2008
    MS-Off Ver
    Excel 2016
    Posts
    23

    Re: Ranking list with duplicate values

    Thanks Cutter,
    That formula works for exactly what I asked, however I don't think I was specific enough. I am hoping to nest the ranking inside of a larger formula in column C, and therefore am not able to use the cell above as a reference. The closest I've come is from a video showing how to create an array formula using INDEX, SMALL, IF, FREQUENCY, and ROW functions that lists the nth largest value the way I need, only I need to find the nth smallest value. Also, in the video it doesn't use the structured references as I would like.

    Here is a link to the video: http://youtu.be/NhRYtZLmtwA , he addresses this @ 7:12

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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