Automatically create a list in date order taken from a number of Grouped fields

[rushdenx1]

The enclosed file (some confidential information has been blacked out) shows groups of customers and when their contract is due for renewal. What I would like is formulas which will automatically create the information shown in the yellow boxes. (columns G-I). I have manually filled in these boxes with 8 entries, but would like the list to include all customers and renewal dates. I am using Office 365 ProPlus. Any help appreciated. Kevin. in the UK.

Example.xlsx
	A	B	C	D	E	F	G	H	I
1	Group 1
2	Customer	Date	Per Month	Provider	Renewal Date		Contracts up for renewal by date
3	Utilities	1st	-£ 180.00	Octopus	13/11/21		Customer	Renewal Date	Days until Renewal
4	Tax	1st	-£ 223.00	SKDC	26/11/21		Insurance	10/10/21	3
5	TV	1st	-£ 67.50	Sky	04/05/22		Bank 1	15/10/21	8
6	Travel	17th	-£ 5.00	Elm Services	01/02/22		Bank 2	16/10/21	9
7	Retail	24th	-£ 9.99	Spotify	03/03/22		Sport	21/10/21	14
8	Medical	26th	-£ 53.00	Virgin Media	19/01/22		Licence	01/11/21	25
9			-£ 538.49				Utilities	13/11/21	37
10	Group 2						Service	20/11/21	44
11	Customer	Cost per yr	Per Month	Provider	Renewal Date		BS	23/11/21	47
12	Water	-£ 488.00	-£ 40.67	Anglian Water	14/02/22
13	Construction	-£ 320.00	-£ 26.67	Direct Line	15/02/22
14	Insurance	-£ 358.00	-£ 29.83	Halifax	10/10/21
15	Licence	-£ 157.50	-£ 13.13	Government	01/11/21
16	Car	-£ 490.00	-£ 40.83	DVLA	01/08/22
17	Service	-£ 1,000.00	-£ 83.33	Vertu	20/11/21
18	Post	-£ 25.00	-£ 2.08	Ring	25/09/22
19	Sport	-£ 105.00	-£ 8.75	Rouvy	21/10/21
20			-£ 245.29
21	Group 3
22	Customer	Type	Rate	Started	Renewal Date
23	Bank 1	Instant Saver			15/10/21
24	Bank 2	1yr Fixed	1.01%	16/10/20	16/10/21
25	Bank 3	1yr Regular	1.05%	16/02/21	16/02/22
26	Bank 4	1yr ISA	0.41%	18/04/21	18/04/22
27	Bank 5	1yr Fixed	1.23%	13/08/21	13/08/22
28
29	Group 4
30	Customer	Type	Rate	Started	Renewal Date
31	Drink	2 yr Fixed	0.71%	23/03/21	23/03/23
32	Leisure	2 yr Fixed	0.76%	01/04/21	01/04/23
33	Commercial	1 yr Fixed	0.59%	14/04/21	14/04/22
34	Train	1yr Fixed	1.00%	09/06/21	09/06/22
35	Airport	95 day notice	0.60%	11/04/21	01/06/23
36	BS	Easy Access	0.45%	01/05/21	23/11/21
37	ISA	Ltd Easy Access	0.55%	26/07/21	12/12/21
Sheet1

Cell Formulas
Range	Formula
C9	C9	=SUM(C3:C8)
C12:C19	C12	=SUM(B12)/12
C20	C20	=SUM(C12:C19)

 

[Fluff]

Hi & welcome to MrExcel.
How about

=LET(Fltr,SORT(FILTER(A3:E37,ISNUMBER(E3:E37)),5),CHOOSE({1,2,3},INDEX(Fltr,,1),INDEX(Fltr,,5),INDEX(Fltr,,5)-TODAY()))

 

[rushdenx1]

Many Thanks for the quick reply. My excel knowledge is pretty basic so where do I put the formula? I have pasted it into a blank cell and it says #NAME?

 

[Fluff]

Ok, you may not have the LET function yet, try

+Fluff 1.xlsm
	A				E	F	G	H	I
1	Group 1
2	Customer				Renewal Date
3	Utilities				13/11/2021		Customer	Renewal Date	Days until Renewal
4	Tax				26/11/2021		Insurance	10/10/2021	3
5	TV				04/05/2022		Bank 1	15/10/2021	8
6	Travel				01/02/2022		Bank 2	16/10/2021	9
7	Retail				03/03/2022		Sport	21/10/2021	14
8	Medical				19/01/2022		Licence	01/11/2021	25
9							Utilities	13/11/2021	37
10	Group 2						Service	20/11/2021	44
11	Customer				Renewal Date		BS	23/11/2021	47
12	Water				14/02/2022		Tax	26/11/2021	50
13	Construction				15/02/2022		ISA	12/12/2021	66
14	Insurance				10/10/2021		Medical	19/01/2022	104
15	Licence				01/11/2021		Travel	01/02/2022	117
16	Car				01/08/2022		Water	14/02/2022	130
17	Service				20/11/2021		Construction	15/02/2022	131
18	Post				25/09/2022		Bank 3	16/02/2022	132
19	Sport				21/10/2021		Retail	03/03/2022	147
20							Commercial	14/04/2022	189
21	Group 3						Bank 4	18/04/2022	193
22	Customer				Renewal Date		TV	04/05/2022	209
23	Bank 1				15/10/2021		Train	09/06/2022	245
24	Bank 2				16/10/2021		Car	01/08/2022	298
25	Bank 3				16/02/2022		Bank 5	13/08/2022	310
26	Bank 4				18/04/2022		Post	25/09/2022	353
27	Bank 5				13/08/2022		Drink	23/03/2023	532
28							Leisure	01/04/2023	541
29	Group 4						Airport	01/06/2023	602
30	Customer				Renewal Date
31	Drink				23/03/2023
32	Leisure				01/04/2023
33	Commercial				14/04/2022
34	Train				09/06/2022
35	Airport				01/06/2023
36	BS				23/11/2021
37	ISA				12/12/2021
Lists

Cell Formulas
Range	Formula
G4:H29	G4	=INDEX(SORT(FILTER(A3:E37,ISNUMBER(E3:E37)),5),SEQUENCE(ROWS(SORT(FILTER(A3:E37,ISNUMBER(E3:E37)),5))),{1,5})
I4:I29	I4	=INDEX(G4#,,2)-TODAY()
Dynamic array formulas.

 

[rushdenx1]

Ok, you may not have the LET function yet, try

+Fluff 1.xlsm
	A				E	F	G	H	I
1	Group 1
2	Customer				Renewal Date
3	Utilities				13/11/2021		Customer	Renewal Date	Days until Renewal
4	Tax				26/11/2021		Insurance	10/10/2021	3
5	TV				04/05/2022		Bank 1	15/10/2021	8
6	Travel				01/02/2022		Bank 2	16/10/2021	9
7	Retail				03/03/2022		Sport	21/10/2021	14
8	Medical				19/01/2022		Licence	01/11/2021	25
9							Utilities	13/11/2021	37
10	Group 2						Service	20/11/2021	44
11	Customer				Renewal Date		BS	23/11/2021	47
12	Water				14/02/2022		Tax	26/11/2021	50
13	Construction				15/02/2022		ISA	12/12/2021	66
14	Insurance				10/10/2021		Medical	19/01/2022	104
15	Licence				01/11/2021		Travel	01/02/2022	117
16	Car				01/08/2022		Water	14/02/2022	130
17	Service				20/11/2021		Construction	15/02/2022	131
18	Post				25/09/2022		Bank 3	16/02/2022	132
19	Sport				21/10/2021		Retail	03/03/2022	147
20							Commercial	14/04/2022	189
21	Group 3						Bank 4	18/04/2022	193
22	Customer				Renewal Date		TV	04/05/2022	209
23	Bank 1				15/10/2021		Train	09/06/2022	245
24	Bank 2				16/10/2021		Car	01/08/2022	298
25	Bank 3				16/02/2022		Bank 5	13/08/2022	310
26	Bank 4				18/04/2022		Post	25/09/2022	353
27	Bank 5				13/08/2022		Drink	23/03/2023	532
28							Leisure	01/04/2023	541
29	Group 4						Airport	01/06/2023	602
30	Customer				Renewal Date
31	Drink				23/03/2023
32	Leisure				01/04/2023
33	Commercial				14/04/2022
34	Train				09/06/2022
35	Airport				01/06/2023
36	BS				23/11/2021
37	ISA				12/12/2021
Lists

Cell Formulas
Range	Formula
G4:H29	G4	=INDEX(SORT(FILTER(A3:E37,ISNUMBER(E3:E37)),5),SEQUENCE(ROWS(SORT(FILTER(A3:E37,ISNUMBER(E3:E37)),5))),{1,5})
I4:I29	I4	=INDEX(G4#,,2)-TODAY()
Dynamic array formulas.

Many Thanks. Works !!

 

[Fluff]

You're welcome & thanks for the feedback.