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

**ransuini** · 08-11-2021, 02:31 PM

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 !!

**JohnTopley** · 08-11-2021, 03:03 PM

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

**ransuini** · 08-11-2021, 03:11 PM

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.

**FlameRetired** · 08-11-2021, 03:17 PM

Originally Posted by ransuini

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.

**JohnTopley** · 08-11-2021, 03:21 PM

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))

**ransuini** · 08-11-2021, 03:41 PM

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

**JohnTopley** · 08-11-2021, 03:59 PM

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

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Automatically Populate a Worksheet (rows/columns) in a Different Workbook based on Value

Can Excel automatically add rows based off the days date?

Can Excel automatically add rows based off the days date?

Can Excel automatically add rows based off the days date?

[SOLVED] Automatically divide cell by days remaining in the week

Remaining days count based on the dates range

Counting the days worked and days remaining on a schedule automatically

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions