+ Reply to Thread
Results 1 to 7 of 7

Have rows automatically populate based on lowest remaining days for a item

  1. #1
    Registered User
    Join Date
    08-11-2021
    Location
    usa
    MS-Off Ver
    2016
    Posts
    4

    Question Have rows automatically populate based on lowest remaining days for a item

    I have a project where I have a lot of contracts sorted by days and purchase numbers.

    To make it easier to see which contracts are expiring the soonest I want to make the top 10 or so rows at the top of the sheet automatically populate with the information of the contracts with the lowest amount of remaining days

    I tried using a small/if function to populate the days at the top, then using index/match to pull the rest of the information into the row from that days number. However, when there were duplicates in the data it would only populate the first instance of it.

    I cant simply do a sort becuase I dont want to move the rows around

    I attached a file with a sample of what i'm trying to do to make it easier to understand.

    Thank You !!
    Attached Files Attached Files
    Last edited by ransuini; 08-11-2021 at 02:43 PM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,632

    Re: Have rows automatically populate based on lowest remaining days for a item

    In A3

    =INDEX(A$11:A$25,MATCH(SMALL($E$11:$E$25,ROWS($1:1)),$E$11:$E$25,0))

    Copy across and down

  3. #3
    Registered User
    Join Date
    08-11-2021
    Location
    usa
    MS-Off Ver
    2016
    Posts
    4

    Re: Have rows automatically populate based on lowest remaining days for a item

    Can you make it not include the values with negative days
    Also the values with the same number of days duplicate all the values instead of moving on the the second instance of it.

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,625

    Re: Have rows automatically populate based on lowest remaining days for a item

    Quote Originally Posted by ransuini View Post
    Can you make it .... Also the values with the same number of days duplicate all the values instead of moving on the the second instance of it.
    Welcome to the forum.

    Please include a sample workbook that shows some of these as well as what and where you want the output.
    Dave

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,632

    Re: Have rows automatically populate based on lowest remaining days for a item

    Helper column in F11 and copy down

    =IF(E11<0,999,RANK(E11,$E$11:$E$25,1)+COUNTIF($E$11:E11,E11)*0.01)

    in A3

    =INDEX(A$11:A$25,MATCH(SMALL($F$11:$F$25,ROWS($1:1)),$F$11:$F$25,0))
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    08-11-2021
    Location
    usa
    MS-Off Ver
    2016
    Posts
    4

    Re: Have rows automatically populate based on lowest remaining days for a item

    Is there any way to do this without adding a helper column, or if not could you tell me what that column exactly does

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,632

    Re: Have rows automatically populate based on lowest remaining days for a item

    First, you can place the helper column anywhere and hide it.

    It ranks the days _ smallest to largest - and for duplicate days it adds an amount (the COUNTIF part) to the RANK value so each occurrence has a unique RANK value.

    For example the 21 days instances are initially RANKed as 3 but we add the COUNTIF( )*0.01 so we get a rank of 3.01 for first occurrence and 3.02 for 2nd occurrence. it would be 3.03 for a third, etc

+ 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: 0
    Last Post: 08-26-2020, 04:30 AM
  2. Can Excel automatically add rows based off the days date?
    By mh42080 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-30-2015, 03:19 PM
  3. Can Excel automatically add rows based off the days date?
    By mh42080 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-30-2015, 02:49 PM
  4. Replies: 1
    Last Post: 01-30-2015, 02:49 PM
  5. [SOLVED] Automatically divide cell by days remaining in the week
    By Granny in forum Excel General
    Replies: 4
    Last Post: 09-01-2012, 02:03 AM
  6. Remaining days count based on the dates range
    By krjoshi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-21-2011, 11:47 AM
  7. Replies: 9
    Last Post: 09-03-2007, 08:26 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