formula to derive list clients who has become overdue

[vinod9111]

Hi,

Need a formula to derive list of clients who has become overdue

Client Date
A 31/3/2017
B 25/3/2017
C 15/3/2017
D 5/3/2017

If the due is 17/3/2017, i want the formula to throw client name C and D who has become overdue.

any help will be appreciated.

regards.
Vinod

 

[Scott T]

Excel 2010
	A	B	C	D	E
1	client	Date		due date	3/17/2017
2	A	3/31/2017		overdue	C
3	B	3/25/2017			D
4	C	3/15/2017
5	D	3/5/2017
6
7
Sheet1

Cell Formulas
Range	Formula
E2	{=IF(ROWS($E$2:E2)>COUNTIF($B$2:$B$5,"<"&$E$1),"",INDEX($A$2:$A$5,SMALL(IF($B$2:$B$5<$E$1,ROW($B$2:$B$5)-ROW($B$2)+1),ROWS($E$2:E2))))}
E3	{=IF(ROWS($E$2:E3)>COUNTIF($B$2:$B$5,"<"&$E$1),"",INDEX($A$2:$A$5,SMALL(IF($B$2:$B$5<$E$1,ROW($B$2:$B$5)-ROW($B$2)+1),ROWS($E$2:E3))))}
E4	{=IF(ROWS($E$2:E4)>COUNTIF($B$2:$B$5,"<"&$E$1),"",INDEX($A$2:$A$5,SMALL(IF($B$2:$B$5<$E$1,ROW($B$2:$B$5)-ROW($B$2)+1),ROWS($E$2:E4))))}
E5	{=IF(ROWS($E$2:E5)>COUNTIF($B$2:$B$5,"<"&$E$1),"",INDEX($A$2:$A$5,SMALL(IF($B$2:$B$5<$E$1,ROW($B$2:$B$5)-ROW($B$2)+1),ROWS($E$2:E5))))}
E6	{=IF(ROWS($E$2:E6)>COUNTIF($B$2:$B$5,"<"&$E$1),"",INDEX($A$2:$A$5,SMALL(IF($B$2:$B$5<$E$1,ROW($B$2:$B$5)-ROW($B$2)+1),ROWS($E$2:E6))))}
E7	{=IF(ROWS($E$2:E7)>COUNTIF($B$2:$B$5,"<"&$E$1),"",INDEX($A$2:$A$5,SMALL(IF($B$2:$B$5<$E$1,ROW($B$2:$B$5)-ROW($B$2)+1),ROWS($E$2:E7))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

Another option.

Excel Workbook
	A	B	C	D	E
1	client	Date		due date	17/03/2017
2	A	31/03/2017		overdue	C
3	B	25/03/2017			D
4	C	15/03/2017
5	D	5/03/2017
6
Overdue

 

[vinod9111]

Thanks Peter and Scott for your prompt help, really appreciated.

 

[vinod9111]

Hi Peter,

Can you also help me understand the formula, how it works.

regards.
Vinod

 

[Peter_SSs]

Thanks Peter and Scott for your prompt help, really appreciated.

You're welcome

 

[Scott T]

Hi Peter,

Can you also help me understand the formula, how it works.

regards.
Vinod

The two formulas are similar but Peter's does not require CSE since Aggregate can handle arrays without CSE.
This returns the row numbers

[COLOR=#800080]ROW([/COLOR][COLOR=teal]$B$2:$B$5[/COLOR][COLOR=#800080])-ROW([/COLOR][COLOR=teal]$B$2[/COLOR][COLOR=#800080])+1[/COLOR]

The dates are tested to see if it is overdue and if so will return true so you would get something like {FALSE;FALSE;3;4}.
the aggregate function 15 is the small function and 6 is to ignore error values.

This is an return 1 then 2 and so on. This is used in the K augment to tell aggregate to return the smallest then the second smallest, 3rd smalls....

This gives the row number for the index function.

[COLOR=#0000ff]ROWS[/COLOR][COLOR=#FF0000](E$2:E2[/COLOR])

You can use evaluate formula on the formulas tab in the ribbon to see what the formula does.

 

[Peter_SSs]

Scott
Thanks for adding explanation. I didn't even notice post #5 as it was at the same time as mine.

 

[vinod9111]

Thanks Scott and Peter, you guys are Champ.

Can we add one more conditions and get the data, if i add payment received date. Now i want to know the clients which are overdue by checking payment received date.



Client Date payment Received date
A 31/3/2017
B 25/3/2017 14/3/2017
C 15/3/2017
D 5/3/2017 5/3/2017
E 7/3/2017

 

[Peter_SSs]

TNow i want to know the clients which are overdue by checking payment received date.

So you want to exclude any clients who have a date in the Payment Received Date?

Excel Workbook
	A	B	C	D	E	F
1	client	Date	P'ment Received		due date	17/03/2017
2	A	31/03/2017			overdue	D
3	B	25/03/2017
4	C	15/03/2017	20/03/2017
5	D	5/03/2017
Overdue (2)