Listing Smallest Pay Periods In Order (multiple duplicates) - Chandoo

[DashboardNovice]

In the attached file, I have multiple pay periods listed, however each pay period appears more than once. I am trying to return the 1st smallest, 2nd smallest, etc.

When I use the SMALL function, the 2nd and 3rd values, etc. are the same as the first value since these pay periods appear more than once.

I am trying to find a way to list the smallest pay period first, followed by the one that is truly the 2nd smallest, then the 3rd smallest, etc.

Cells H6:H10 contain what the desired results would be.

 

[Khalid NGO]

Hi,
Check this with CSE in H6:
=IFERROR(INDEX($D$5:$D$74,MATCH(0,COUNTIF(H$5:H5,$D$5:$D$74),0)),"")

copy down.

Regards,

 

[DashboardNovice]

Hi,
Check this with CSE in H6:
=IFERROR(INDEX($D$5:$D$74,MATCH(0,COUNTIF(H$5:H5,$D$5:$D$74),0)),"")

copy down.

Regards,

Thank you for your reply. I tried your suggestion but I couldn't get it to work. I put the formula in H6 like you suggested. The COUNTIF refers to cell H5 which is blank. What does that refer to?

I thought you might have meant D instead of H in the first COUNTIF argument, but I couldn't get that to work.

 

[prasaddn]

Hi,

Define Name: Range1 as =Sheet1!$D$5:$D$74

You need to apply this formula for E2:
=SUMPRODUCT((Range1<>"")/COUNTIF(Range1,Range1))
This will fetch you number of unique values.

And the below CSE formula can be pasted anywhere in the and dragged down to get the unique list of dates:
=IF(ROWS($1:1)>$E$2,"",INDEX(Range1,MATCH(SMALL(IF(FREQUENCY(IF(Range1<>"",MATCH(Range1,Range1,0)),ROW(INDIRECT("1:"&ROWS(Range1)))),COUNTIF(Range1,"<"&Range1)),ROWS($1:1)),IF(Range1<>"",COUNTIF(Range1,"<"&Range1)),0)))

Let me know if this helps.

Regards,
PrasadDN

 

[DashboardNovice]

Hi,

Define Name: Range1 as =Sheet1!$D$5:$D$74

You need to apply this formula for E2:
=SUMPRODUCT((Range1<>"")/COUNTIF(Range1,Range1))
This will fetch you number of unique values.

And the below CSE formula can be pasted anywhere in the and dragged down to get the unique list of dates:
=IF(ROWS($1:1)>$E$2,"",INDEX(Range1,MATCH(SMALL(IF(FREQUENCY(IF(Range1<>"",MATCH(Range1,Range1,0)),ROW(INDIRECT("1:"&ROWS(Range1)))),COUNTIF(Range1,"<"&Range1)),ROWS($1:1)),IF(Range1<>"",COUNTIF(Range1,"<"&Range1)),0)))

Let me know if this helps.

Regards,
PrasadDN

I'm not trying to count the unique values. I modified my spreadsheet.

Formulas will go in E10:E14. To get started I had to hard code the first two smallest pay period dates in E10:E11. The values in column F are the results I am expecting to get in column E. I am starting in E12.

My concern is the 2nd argument of COUNTIF. I figure that if I can get a count of the values in the preceding cells (E10:E11), then add 1, I should be able to get the next date in line (02/06).

 

[prasaddn]

Hi,

The file not only gives you unique values but the your initial requirement of consecutive pay periods, (small, k).

Even with your new row wise data, you can use transpose() for Range1, and the result.

Coming to your specific countif formula, the below formula works, but with one caveat, that the data should be sorted!

Apply below formula to E11 as CSE:
=INDEX(Range1,1,SUM(COUNTIF(Range1,$E$10:E10))+1)

The first date in E10 will have to be updated manually.

Regards,
Prasad DN.
PS: Again, Range1 is defined name with range of your data.

 

[prasaddn]

Hi,

I hope the above solution provides you what you wanted.

In case you wish to get the same when dates are not sorted, or by not entering the first date manually, here is the formula, non CSE:

if Dates are in row:
Define Name:
Range1 = Transpose($H6:$BY$6)

if Dates are in Column:
Define Name:
Range1 = Sheet1!$D$5:$D$74

And the main formula is:
=SMALL(MODE.MULT(IF({1,1},IF(FREQUENCY(Range1,Range1),Range1))),ROWS($1:1))

And drag down.

Regards,
PrasadDN
Edited: Row() to Rows($1:1) so that formula can be placed in any cell of sheet.

 

[Khalid NGO]

Thank you for your reply. I tried your suggestion but I couldn't get it to work. I put the formula in H6 like you suggested. The COUNTIF refers to cell H5 which is blank. What does that refer to?

I thought you might have meant D instead of H in the first COUNTIF argument, but I couldn't get that to work.

Hi,
Your confusion is right,
but you have to go with it...

=INDEX($D$5:$D$74,MATCH(0,COUNTIF(H$5:H5,$D$5:$D$74),0))

see the "MATCH(0,COUNTIF(H$5:H5" part

for the first blank (H5) the array would be:
{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
and MATCH will return 1 (which is 09/01/2015)

when you copy it down, criteria range will be increase row-wise.
for example for H6:
{1;1;1;1;1;1;1;1;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
MATCH will return 15 (which is 23/01/2015)

copying it down will return the unique.

I may have not explained in detail but see the attached..
and I am sure you are familiar with remaining parts =IFERROR(INDEX

Remember to enter it with CSE
Regards,