Put Age into a Particular set age group bracket display

[Leicester City Fox]

Hi All

Good Morning

I have a set of data and I work out their age from the DOB. I was going to group the Ages in a pivot age group bracket but can’t get it to fit the age bracket displayed given below any ideas how to get my data into Age Group requested?

Age Group Specified:

29 or Under

30-39

40-49

50-59

60-69

70-79

80+

Be grateful for the best and assist way to set this ag grouping above.



Many Thanks

The Leicester Fox

 

[KRice]

You could do this with a rather complicated formula as shown here...note that I've made some interpretations of the age brackets in that a person's years must be greater than or equal to the lower bound and less than the upper bound. The formula for extracting the list of names is placed in cell B5 (in this case) and then copied across to H5. Then B5:H5 is selected and dragged down until no more names are added to any of the columns.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1												3/26/2020	<--Today
2	>=	0	30	40	50	60	70	80					Age
3	<	30	40	50	60	70	80	999		Names	DOB	years	months	days
4	Age Group Specified:	29 or Under	30-39	40-49	50-59	60-69	70-79	80+		Keon Rogers	6/15/1937	82	9	11
5		Cherish Rice	Alonzo Nash	Kadence Gilmore	Malcolm Shaw	Angelo Zuniga	Seth Flynn	Keon Rogers		Cherish Rice	11/30/1996	23	3	25
6		Aria Morse	Kate Crawford	Coby Jacobson	Van Randolph	Amani Riddle	Koen Bird	Shayna House		Aria Morse	9/5/2009	10	6	21
7		Charlie Moore	Morgan Sullivan	Jocelyn Mills	Sarai Newman	Sophia Barton	Lauryn Gallagher	Aimee Pacheco		Malcolm Shaw	9/22/1961	58	6	4
8		Lilah Shepherd	Mira Park	Lewis Hampton	Kayden Fischer	Janae Montgomery	Anabel Ayers	Penelope Harris		Angelo Zuniga	10/3/1953	66	5	23
9		Phoenix Lucas	Katherine Frey	Makhi Knox	Ashlyn Cisneros	Sophia Cooley	Chloe Li	Pedro Powell		Seth Flynn	10/18/1940	79	5	8
10		Conner Shaffer	Solomon Hancock	Lennon Middleton	Van Barr	Zayne Burgess	Cason Oneal	Rebekah Rush		Kadence Gilmore	9/22/1979	40	6	4
11		Rosa Villa	Kieran Fitzgerald	Dalton Hickman	Royce Love	Gisselle Frost	Faith Washington	Elianna Munoz		Koen Bird	4/24/1941	78	11	2
12		Amara Ramirez	Arturo Rangel	Brenna Ferguson	Callie Bruce		Jacquelyn Lamb	Madelyn Huerta		Shayna House	2/29/1932	88	0	26
13		Shania Waters	Terrance Howe	Jaidyn Dodson	Jaqueline Randolph		Susan Carrillo	Kadin Pierce		Aimee Pacheco	7/20/1934	85	8	6
14		Brooklynn Cortez		Rashad Clark	Lilia Kennedy			Emiliano Adkins		Amani Riddle	10/7/1952	67	5	19
15		Felicity Williamson		Avery Hebert	Roy Ponce			Erica Harrington		Van Randolph	3/11/1969	51	0	15
16		Uriel Daugherty			Donte Michael					Penelope Harris	8/16/1936	83	7	10
17		Cristian Dalton			Cameron Goodman					Sophia Barton	7/27/1950	69	7	28
18		Tori Estrada			Kaleb Jordan					Sarai Newman	11/18/1967	52	4	8
19		Brice Bryan			Easton Mullen					Pedro Powell	3/11/1933	87	0	15
20					Rebekah Watkins					Rebekah Rush	10/19/1930	89	5	7
21					Dean Leonard					Coby Jacobson	3/25/1975	45	0	1
22					Brooklynn Nunez					Jocelyn Mills	10/5/1973	46	5	21
23										Charlie Moore	6/18/2003	16	9	8
24										Alonzo Nash	5/12/1988	31	10	14
25										Elianna Munoz	12/5/1934	85	3	21
26										Lauryn Gallagher	10/9/1941	78	5	17
27										Lilah Shepherd	1/28/2002	18	1	27
28										Kayden Fischer	7/3/1967	52	8	23
29										Phoenix Lucas	1/24/2005	15	2	2
30										Kate Crawford	11/17/1984	35	4	9
31										Conner Shaffer	3/16/2008	12	0	10
32										Janae Montgomery	4/22/1953	66	11	4
33										Ashlyn Cisneros	12/30/1964	55	2	25
34										Lewis Hampton	6/11/1976	43	9	15
35										Rosa Villa	1/1/2005	15	2	25
36										Makhi Knox	4/10/1972	47	11	16
37										Van Barr	11/26/1967	52	4	0
38										Royce Love	9/4/1964	55	6	22
39										Callie Bruce	12/13/1965	54	3	13
40										Lennon Middleton	9/16/1973	46	6	10
41										Jaqueline Randolph	3/3/1964	56	0	23
42										Lilia Kennedy	5/11/1966	53	10	15
43										Roy Ponce	6/1/1964	55	9	25
44										Sophia Cooley	3/18/1951	69	0	8
45										Morgan Sullivan	2/26/1989	31	1	0
46										Dalton Hickman	10/3/1978	41	5	23
47										Mira Park	5/25/1983	36	10	1
48										Amara Ramirez	7/22/1990	29	8	4
49										Shania Waters	1/19/2003	17	2	7
50										Brenna Ferguson	6/23/1979	40	9	3
51										Donte Michael	9/9/1966	53	6	17
52										Madelyn Huerta	1/6/1934	86	2	20
53										Katherine Frey	4/30/1987	32	10	25
54										Anabel Ayers	12/27/1944	75	2	28
55										Solomon Hancock	6/8/1981	38	9	18
56										Chloe Li	7/23/1941	78	8	3
57										Cason Oneal	9/6/1944	75	6	20
58										Zayne Burgess	2/26/1958	62	1	0
59										Cameron Goodman	11/8/1969	50	4	18
60										Brooklynn Cortez	1/16/2004	16	2	10
61										Kieran Fitzgerald	7/2/1980	39	8	24
62										Jaidyn Dodson	11/3/1978	41	4	23
63										Arturo Rangel	12/8/1980	39	3	18
64										Terrance Howe	10/29/1981	38	4	26
65										Rashad Clark	8/12/1972	47	7	14
66										Kaleb Jordan	7/29/1964	55	7	26
67										Easton Mullen	7/4/1963	56	8	22
68										Kadin Pierce	3/16/1937	83	0	10
69										Rebekah Watkins	6/16/1968	51	9	10
70										Emiliano Adkins	7/4/1936	83	8	22
71										Avery Hebert	10/21/1977	42	5	5
72										Felicity Williamson	9/24/1996	23	6	2
73										Uriel Daugherty	11/19/2009	10	4	7
74										Cristian Dalton	6/20/1992	27	9	6
75										Tori Estrada	5/14/1993	26	10	12
76										Faith Washington	10/25/1945	74	5	1
77										Erica Harrington	6/27/1938	81	8	28
78										Jacquelyn Lamb	8/4/1946	73	7	22
79										Susan Carrillo	12/15/1942	77	3	11
80										Dean Leonard	3/18/1967	53	0	8
81										Brooklynn Nunez	7/21/1963	56	8	5
82										Brice Bryan	8/21/2005	14	7	5
83										Gisselle Frost	2/10/1956	64	1	16
Sheet1

Cell Formulas
Range	Formula
L1	L1	=TODAY()
L4:L83	L4	=DATEDIF($K4,$L$1,"Y")
M4:M83	M4	=DATEDIF($K4,$L$1,"YM")
N4:N83	N4	=DATEDIF($K4,$L$1,"MD")
B25,B5:H24	B5	=IF(ROWS($J$4:J4)<=COUNTIFS($L$4:$L$83,">="&B$2,$L$4:$L$83,"<"&B$3),INDEX($J$4:$J$83,AGGREGATE(15,3,((($L$4:$L$83>=B$2)*($L$4:$L$83<B$3))/(($L$4:$L$83>=B$2)*($L$4:$L$83<B$3))*ROW($L$4:$L$83))-ROW($L$3), ROWS($B$5:B5))),"")
C25:H25	C25	=IF(ROWS($J$4:J24)<=COUNTIFS($L$4:$L$83,">="&C$2,$L$4:$L$83,"<"&C$3),INDEX($J$4:$J$83,AGGREGATE(15,3,((($L$4:$L$83>=C$2)*($L$4:$L$83<C$3))/(($L$4:$L$83>=C$2)*($L$4:$L$83<C$3))*ROW($L$4:$L$83))-ROW($L$3), ROWS($B$5:C25))),"")

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

For example, IF you have Excel 365 with the FILTER function, you simply need to put this formula in B5 and copy across row 5. The other results will automatically "spill" down the relevant columns.

20 03 27.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1										27/03/2020	<--Today
2	>=	0	30	40	50	60	70	80
3	<	30	40	50	60	70	80	120		Names	DOB
4	Age Group	29 or Under	30-39	40-49	50-59	60-69	70-79	80+		Keon Rogers	15/06/1937
5		Cherish Rice	Alonzo Nash	Kadence Gilmore	Malcolm Shaw	Angelo Zuniga	Seth Flynn	Keon Rogers		Cherish Rice	30/11/1996
6		Aria Morse	Kate Crawford	Coby Jacobson	Van Randolph	Amani Riddle	Koen Bird	Shayna House		Aria Morse	5/09/2009
7		Charlie Moore	Morgan Sullivan	Jocelyn Mills	Sarai Newman	Sophia Barton	Lauryn Gallagher	Aimee Pacheco		Malcolm Shaw	22/09/1961
8		Lilah Shepherd	Mira Park	Lewis Hampton	Kayden Fischer	Janae Montgomery	Anabel Ayers	Penelope Harris		Angelo Zuniga	3/10/1953
9		Phoenix Lucas	Katherine Frey	Makhi Knox	Ashlyn Cisneros	Sophia Cooley	Chloe Li	Pedro Powell		Seth Flynn	18/10/1940
10		Conner Shaffer	Solomon Hancock	Lennon Middleton	Van Barr	Zayne Burgess	Cason Oneal	Rebekah Rush		Kadence Gilmore	22/09/1979
11		Rosa Villa	Kieran Fitzgerald	Dalton Hickman	Royce Love	Gisselle Frost	Faith Washington	Elianna Munoz		Koen Bird	24/04/1941
12		Amara Ramirez	Arturo Rangel	Brenna Ferguson	Callie Bruce		Jacquelyn Lamb	Madelyn Huerta		Shayna House	29/02/1932
13		Shania Waters	Terrance Howe	Jaidyn Dodson	Jaqueline Randolph		Susan Carrillo	Kadin Pierce		Aimee Pacheco	20/07/1934
14		Brooklynn Cortez		Rashad Clark	Lilia Kennedy			Emiliano Adkins		Amani Riddle	7/10/1952
15		Felicity Williamson		Avery Hebert	Roy Ponce			Erica Harrington		Van Randolph	11/03/1969
16		Uriel Daugherty			Donte Michael					Penelope Harris	16/08/1936
17		Cristian Dalton			Cameron Goodman					Sophia Barton	27/07/1950
18		Tori Estrada			Kaleb Jordan					Sarai Newman	18/11/1967
19		Brice Bryan			Easton Mullen					Pedro Powell	11/03/1933
20					Rebekah Watkins					Rebekah Rush	19/10/1930
21					Dean Leonard					Coby Jacobson	25/03/1975
22					Brooklynn Nunez					Jocelyn Mills	5/10/1973
23										Charlie Moore	18/06/2003
24										Alonzo Nash	12/05/1988
25										Elianna Munoz	5/12/1934
26										Lauryn Gallagher	9/10/1941
27										Lilah Shepherd	28/01/2002
28										Kayden Fischer	3/07/1967
29										Phoenix Lucas	24/01/2005
30										Kate Crawford	17/11/1984
31										Conner Shaffer	16/03/2008
32										Janae Montgomery	22/04/1953
33										Ashlyn Cisneros	30/12/1964
34										Lewis Hampton	11/06/1976
35										Rosa Villa	1/01/2005
36										Makhi Knox	10/04/1972
37										Van Barr	26/11/1967
38										Royce Love	4/09/1964
39										Callie Bruce	13/12/1965
40										Lennon Middleton	16/09/1973
41										Jaqueline Randolph	3/03/1964
42										Lilia Kennedy	11/05/1966
43										Roy Ponce	1/06/1964
44										Sophia Cooley	18/03/1951
45										Morgan Sullivan	26/02/1989
46										Dalton Hickman	3/10/1978
47										Mira Park	25/05/1983
48										Amara Ramirez	22/07/1990
49										Shania Waters	19/01/2003
50										Brenna Ferguson	23/06/1979
51										Donte Michael	9/09/1966
52										Madelyn Huerta	6/01/1934
53										Katherine Frey	30/04/1987
54										Anabel Ayers	27/12/1944
55										Solomon Hancock	8/06/1981
56										Chloe Li	23/07/1941
57										Cason Oneal	6/09/1944
58										Zayne Burgess	26/02/1958
59										Cameron Goodman	8/11/1969
60										Brooklynn Cortez	16/01/2004
61										Kieran Fitzgerald	2/07/1980
62										Jaidyn Dodson	3/11/1978
63										Arturo Rangel	8/12/1980
64										Terrance Howe	29/10/1981
65										Rashad Clark	12/08/1972
66										Kaleb Jordan	29/07/1964
67										Easton Mullen	4/07/1963
68										Kadin Pierce	16/03/1937
69										Rebekah Watkins	16/06/1968
70										Emiliano Adkins	4/07/1936
71										Avery Hebert	21/10/1977
72										Felicity Williamson	24/09/1996
73										Uriel Daugherty	19/11/2009
74										Cristian Dalton	20/06/1992
75										Tori Estrada	14/05/1993
76										Faith Washington	25/10/1945
77										Erica Harrington	27/06/1938
78										Jacquelyn Lamb	4/08/1946
79										Susan Carrillo	15/12/1942
80										Dean Leonard	18/03/1967
81										Brooklynn Nunez	21/07/1963
82										Brice Bryan	21/08/2005
83										Gisselle Frost	10/02/1956
Ages Dynamic Arrays

Cell Formulas
Range	Formula
J1	J1	=TODAY()
B5:B19,H5:H15,G5:G13,F5:F11,E5:E22,D5:D15,C5:C13	B5	=FILTER($J$4:$J$83,($K$4:$K$83<=EDATE($J$1,-12*B$2))*($K$4:$K$83>EDATE($J$1,-12*B$3)))
Dynamic array formulas.

IF you don't have FILTER but do have Excel 2010 or later ..
This B5 formula copied across and down

20 03 27.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1										27/03/2020	<--Today
2	>=	0	30	40	50	60	70	80
3	<	30	40	50	60	70	80	120		Names	DOB
4	Age Group	29 or Under	30-39	40-49	50-59	60-69	70-79	80+		Keon Rogers	15/06/1937
5		Cherish Rice	Alonzo Nash	Kadence Gilmore	Malcolm Shaw	Angelo Zuniga	Seth Flynn	Keon Rogers		Cherish Rice	30/11/1996
6		Aria Morse	Kate Crawford	Coby Jacobson	Van Randolph	Amani Riddle	Koen Bird	Shayna House		Aria Morse	5/09/2009
7		Charlie Moore	Morgan Sullivan	Jocelyn Mills	Sarai Newman	Sophia Barton	Lauryn Gallagher	Aimee Pacheco		Malcolm Shaw	22/09/1961
8		Lilah Shepherd	Mira Park	Lewis Hampton	Kayden Fischer	Janae Montgomery	Anabel Ayers	Penelope Harris		Angelo Zuniga	3/10/1953
9		Phoenix Lucas	Katherine Frey	Makhi Knox	Ashlyn Cisneros	Sophia Cooley	Chloe Li	Pedro Powell		Seth Flynn	18/10/1940
10		Conner Shaffer	Solomon Hancock	Lennon Middleton	Van Barr	Zayne Burgess	Cason Oneal	Rebekah Rush		Kadence Gilmore	22/09/1979
11		Rosa Villa	Kieran Fitzgerald	Dalton Hickman	Royce Love	Gisselle Frost	Faith Washington	Elianna Munoz		Koen Bird	24/04/1941
12		Amara Ramirez	Arturo Rangel	Brenna Ferguson	Callie Bruce		Jacquelyn Lamb	Madelyn Huerta		Shayna House	29/02/1932
13		Shania Waters	Terrance Howe	Jaidyn Dodson	Jaqueline Randolph		Susan Carrillo	Kadin Pierce		Aimee Pacheco	20/07/1934
14		Brooklynn Cortez		Rashad Clark	Lilia Kennedy			Emiliano Adkins		Amani Riddle	7/10/1952
15		Felicity Williamson		Avery Hebert	Roy Ponce			Erica Harrington		Van Randolph	11/03/1969
16		Uriel Daugherty			Donte Michael					Penelope Harris	16/08/1936
17		Cristian Dalton			Cameron Goodman					Sophia Barton	27/07/1950
18		Tori Estrada			Kaleb Jordan					Sarai Newman	18/11/1967
19		Brice Bryan			Easton Mullen					Pedro Powell	11/03/1933
20					Rebekah Watkins					Rebekah Rush	19/10/1930
21					Dean Leonard					Coby Jacobson	25/03/1975
22					Brooklynn Nunez					Jocelyn Mills	5/10/1973
23										Charlie Moore	18/06/2003
24										Alonzo Nash	12/05/1988
25										Elianna Munoz	5/12/1934
26										Lauryn Gallagher	9/10/1941
27										Lilah Shepherd	28/01/2002
28										Kayden Fischer	3/07/1967
29										Phoenix Lucas	24/01/2005
30										Kate Crawford	17/11/1984
31										Conner Shaffer	16/03/2008
32										Janae Montgomery	22/04/1953
33										Ashlyn Cisneros	30/12/1964
34										Lewis Hampton	11/06/1976
35										Rosa Villa	1/01/2005
36										Makhi Knox	10/04/1972
37										Van Barr	26/11/1967
38										Royce Love	4/09/1964
39										Callie Bruce	13/12/1965
40										Lennon Middleton	16/09/1973
41										Jaqueline Randolph	3/03/1964
42										Lilia Kennedy	11/05/1966
43										Roy Ponce	1/06/1964
44										Sophia Cooley	18/03/1951
45										Morgan Sullivan	26/02/1989
46										Dalton Hickman	3/10/1978
47										Mira Park	25/05/1983
48										Amara Ramirez	22/07/1990
49										Shania Waters	19/01/2003
50										Brenna Ferguson	23/06/1979
51										Donte Michael	9/09/1966
52										Madelyn Huerta	6/01/1934
53										Katherine Frey	30/04/1987
54										Anabel Ayers	27/12/1944
55										Solomon Hancock	8/06/1981
56										Chloe Li	23/07/1941
57										Cason Oneal	6/09/1944
58										Zayne Burgess	26/02/1958
59										Cameron Goodman	8/11/1969
60										Brooklynn Cortez	16/01/2004
61										Kieran Fitzgerald	2/07/1980
62										Jaidyn Dodson	3/11/1978
63										Arturo Rangel	8/12/1980
64										Terrance Howe	29/10/1981
65										Rashad Clark	12/08/1972
66										Kaleb Jordan	29/07/1964
67										Easton Mullen	4/07/1963
68										Kadin Pierce	16/03/1937
69										Rebekah Watkins	16/06/1968
70										Emiliano Adkins	4/07/1936
71										Avery Hebert	21/10/1977
72										Felicity Williamson	24/09/1996
73										Uriel Daugherty	19/11/2009
74										Cristian Dalton	20/06/1992
75										Tori Estrada	14/05/1993
76										Faith Washington	25/10/1945
77										Erica Harrington	27/06/1938
78										Jacquelyn Lamb	4/08/1946
79										Susan Carrillo	15/12/1942
80										Dean Leonard	18/03/1967
81										Brooklynn Nunez	21/07/1963
82										Brice Bryan	21/08/2005
83										Gisselle Frost	10/02/1956
Ages Excel 2010+

Cell Formulas
Range	Formula
J1	J1	=TODAY()
B5:H24	B5	=IFERROR(INDEX($J:$J,AGGREGATE(15,6,ROW($J$4:$J$83)/(($K$4:$K$83<=EDATE($J$1,-12*B$2))*($K$4:$K$83>EDATE($J$1,-12*B$3))),ROWS(B$5:B5))),"")