Produce list of significant birthdays (birthdays ending in a 0)

[Jasesair]

I have all the data needed but am struggling to create a working formula to show significant birthdays.

AA6:AA203 has ages as at 31 Dec
B6:B203 has everyone's names
C6:C203 has everyone's DOB

I think I'm wanting to identify the nth occurrence of column AA range ending in a 0, then return their name and their DOB. Ideally, if the formula could include significant birthdays of 18 and 21 as well as ones ending in 0, that would be amazing! I've been trying to avoid array formulas. Any assistance would be brilliant!

 

[Norie]

What do you mean by 'nth occurrence'?

nth occurrence of column A

 

[Jasesair]

1st occurrence, 2nd occurrence, 3rd occurrence...

 

[Norie]

Do you mean you want to get the birthdays for every person who's age ends with a 0 and everybody who will be 18/21?

 

[Jasesair]

Yes!

 

[JGordon11]

Significant bdays is the only column requiring array formulas (ctrl + shift + enter on the first one then drag down). Data table actually goes down to 200th row just showing thru row 27 below. You'll need to adjust the formulas to reflect where your data is located

MrE20210129.xlsm
	A	B	C	D	E	F	G	H	I
1	Name	DOB	Age	Next bday significant	Next bday		Name	Current Age	Significant bdays
2	Name1	1/19/1935	86	FALSE	1/19/2022		Name91	39	2/7/2021
3	Name2	1/13/1937	84	FALSE	1/13/2022		Name133	89	2/8/2021
4	Name3	10/20/1932	88	FALSE	10/20/2021		Name6	39	2/20/2021
5	Name4	1/18/1963	58	FALSE	1/18/2022		Name53	99	4/2/2021
6	Name5	9/15/1951	69	TRUE	9/15/2021		Name190	19	4/6/2021
7	Name6	2/20/1981	39	TRUE	2/20/2021		Name123	79	4/16/2021
8	Name7	5/3/1950	70	FALSE	5/3/2021		Name89	69	5/10/2021
9	Name8	6/6/1956	64	FALSE	6/6/2021		Name189	99	5/20/2021
10	Name9	11/5/1953	67	FALSE	11/5/2021		Name47	89	6/18/2021
11	Name10	8/18/1953	67	FALSE	8/18/2021		Name83	79	6/30/2021
12	Name11	10/22/1988	32	FALSE	10/22/2021		Name135	79	7/20/2021
13	Name12	11/14/2005	15	FALSE	11/14/2021		Name114	9	7/24/2021
14	Name13	1/3/2001	20	TRUE	1/3/2022		Name186	49	8/13/2021
15	Name14	8/21/1955	65	FALSE	8/21/2021		Name5	69	9/15/2021
16	Name15	7/16/1920	100	FALSE	7/16/2021		Name93	9	11/8/2021
17	Name16	5/4/1937	83	FALSE	5/4/2021		Name86	29	12/5/2021
18	Name17	6/9/1920	100	FALSE	6/9/2021		Name58	19	12/10/2021
19	Name18	3/1/1924	96	FALSE	3/1/2021		Name13	20	1/3/2022
20	Name19	3/24/1920	100	FALSE	3/24/2021		Name148	29	1/9/2022
21	Name20	7/11/1922	98	FALSE	7/11/2021
22	Name21	8/25/1974	46	FALSE	8/25/2021
23	Name22	9/13/1952	68	FALSE	9/13/2021
24	Name23	12/10/1948	72	FALSE	12/10/2021
25	Name24	4/22/1925	95	FALSE	4/22/2021
26	Name25	8/30/1950	70	FALSE	8/30/2021
27	Name26	11/20/2012	8	FALSE	11/20/2021
Sheet2

Cell Formulas
Range	Formula
D2:D27	D2	=OR(RIGHT(C2,1) & ""="9",C2=17,C2=20)
E2:E27	E2	=IF(DATE(YEAR(TODAY()), MONTH(B2),DAY(B2)) < TODAY(), DATE(YEAR(TODAY())+1, MONTH(B2),DAY(B2)),DATE(YEAR(TODAY()), MONTH(B2),DAY(B2)))+ ROW()/100000
G2:G25	G2	=IFERROR(INDEX($A$2:$A$200,MATCH(I2,$E$2:$E$200,0)),"")
H2:H25	H2	=IFERROR(INDEX($C$2:$C$200,MATCH(I2,$E$2:$E$200,0)),"")
I2:I25	I2	=IFERROR(AGGREGATE(15,6,$E$2:$E$200*(1/$D$2:$D$200),ROW()-1),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Peter_SSs]

identify the nth occurrence of column AA range ending in a 0, then return their name and their DOB. Ideally, if the formula could include significant birthdays of 18 and 21 as well as ones ending in 0,

See if this does what you want.

Jaseair.xlsm
	B	C																								AA	AB	AC	AD	AE
1	Name	DoB																								Age this year		Name	DoB	Sig. B'day
2	Name 1	11/04/1967																								54		Name 4	24/10/2003	18
3	Name 2	15/04/1982																								39		Name 5	20/12/1971	50
4	Name 3	25/12/1988																								33		Name 6	20/05/1981	40
5	Name 4	24/10/2003																								18		Name 8	21/01/2000	21
6	Name 5	20/12/1971																								50		Name 11	9/03/2000	21
7	Name 6	20/05/1981																								40		Name 16	21/02/1931	90
8	Name 7	5/07/1994																								27		Name 18	16/11/1981	40
9	Name 8	21/01/2000																								21		Name 19	20/11/1981	40
10	Name 9	25/02/1975																								46
11	Name 10	30/09/1986																								35
12	Name 11	9/03/2000																								21
13	Name 12	20/11/1989																								32
14	Name 13	18/10/1989																								32
15	Name 14	18/03/1995																								26
16	Name 15	28/08/1978																								43
17	Name 16	21/02/1931																								90
18	Name 17	11/09/2002																								19
19	Name 18	16/11/1981																								40
20	Name 19	20/11/1981																								40
Birthdays

Cell Formulas
Range	Formula
AC2:AC20	AC2	=IF(AE2="","",INDEX(B:B,AGGREGATE(15,6,ROW(B$2:B$20)/(AA$2:AA$20=AE2),COUNTIF(AE$2:AE2,AE2))))
AD2:AD20	AD2	=IF(AC2="","",VLOOKUP(AC2,B$2:C$20,2,0))
AE2:AE20	AE2	=IFERROR(INDEX(AA:AA,AGGREGATE(15,6,ROW(AA$2:AA$20)/((RIGHT(AA$2:AA$20,1)="0")+(AA$2:AA$20=18)+(AA$2:AA$20=21)),ROWS(AE$2:AE2))),"")
AA2:AA20	AA2	=YEAR(DATE(YEAR(TODAY()),12,31))-YEAR(C2)

 

[Peter_SSs]

.. or perhaps this slight variation with the significant birthdays in ascending order.

Jaseair.xlsm
	B	C																								AA	AB	AC	AD	AE
1	Name	DoB																								Age this year		Name	DoB	Sig. B'day
2	Name 1	11/04/1967																								54		Name 4	24/10/2003	18
3	Name 2	15/04/1982																								39		Name 8	21/01/2000	21
4	Name 3	25/12/1988																								33		Name 11	9/03/2000	21
5	Name 4	24/10/2003																								18		Name 6	20/05/1981	40
6	Name 5	20/12/1971																								50		Name 18	16/11/1981	40
7	Name 6	20/05/1981																								40		Name 19	20/11/1981	40
8	Name 7	5/07/1994																								27		Name 5	20/12/1971	50
9	Name 8	21/01/2000																								21		Name 16	21/02/1931	90
10	Name 9	25/02/1975																								46
11	Name 10	30/09/1986																								35
12	Name 11	9/03/2000																								21
13	Name 12	20/11/1989																								32
14	Name 13	18/10/1989																								32
15	Name 14	18/03/1995																								26
16	Name 15	28/08/1978																								43
17	Name 16	21/02/1931																								90
18	Name 17	11/09/2002																								19
19	Name 18	16/11/1981																								40
20	Name 19	20/11/1981																								40
Birthdays (2)

Cell Formulas
Range	Formula
AC2:AC20	AC2	=IF(AE2="","",INDEX(B:B,AGGREGATE(15,6,ROW(B$2:B$20)/(AA$2:AA$20=AE2),COUNTIF(AE$2:AE2,AE2))))
AD2:AD20	AD2	=IF(AC2="","",VLOOKUP(AC2,B$2:C$20,2,0))
AE2:AE20	AE2	=IFERROR(AGGREGATE(15,6,AA$2:AA$20/((MOD(AA$2:AA$20,10)=0)+(AA$2:AA$20=18)+(AA$2:AA$20=21)),ROWS(AE$2:AE2)),"")
AA2:AA20	AA2	=YEAR(DATE(YEAR(TODAY()),12,31))-YEAR(C2)

 

[Jasesair]

I rudely didn't send you any thanks and appreciation for this! It's exactly what I needed and works perfectly.

 

[Peter_SSs]

No problem. Glad it worked for you.