birthdays list

[KlausW]

Hi
I want to make a birthday list where the months come in a cell for themselves and the birthdays come down there after. Starting with January. The challenges are that new people come every year so the birthdays are not the same year after year. Someone who can help.
The red are my formulas and they are hidden
The dates are in danish dd/mm/yy
The file is uploadet to Dropbox Birthday
All help will be appreciated
Regards Klaus W

 

[Dave Patton]

T202009c.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	N/C= No Condition		No Active Filter						No Active Filter		No Active Filter
2							17 is the Count
3	Dato	Dag	Navn	Skibs nr.	Alder	31-12-20	CPR	DOB	MDR.	Dob2	Month	1	Jan
4	18		John		29	januar	200158-1111	20-Jan-58	1	20-Jan	Jan	2	Feb
5	20		Ole		62	februar	040272-1111	4-Feb-72	2	04-Feb	Feb	3	Mar
6	04		Anne		48	marts	040392-1111	4-Mar-92	3	04-Mar	Mar	4	Apr
7	14		Dorte		32	marts	290375-1111	29-Mar-75	3	29-Mar	Mar	5	May
8	04		Claus		28	april	080466-1111	8-Apr-66	4	08-Apr	Apr	6	Jun
9	29		Ove		45	maj	050599-1111	5-May-99	5	05-May	May	7	Jul
10	04		Ida		22	maj	050502-1111	5-May-02	5	05-May	May	8	Aug
11	08		Mette		54	juni	090674-1111	9-Jun-74	6	09-Jun	Jun	9	Sep
12	05		Per		21	juli	300789-1111	30-Jul-89	7	30-Jul	Jul	10	Oct
13	22		Leo		59	august	030888-1111	3-Aug-88	8	03-Aug	Aug	11	Nov
14	05		Klaus		18	august	240894-1111	24-Aug-94	8	24-Aug	Aug	12	Dec
15	09		Peter		46	september	160976-1111	16-Sep-76	9	16-Sep	Sep
16	11		Carl		42	oktober	271099-1111	27-Oct-99	10	27-Oct	Oct
17	30		Ulla		31	november	291174-1111	29-Nov-74	11	29-Nov	Nov
18	03		Ib		32	december	241288-1111	24-Dec-88	12	24-Dec	Dec
19	24		Kaj		26	december	251200-1111	25-Dec-00	12	25-Dec	Dec
20			XXX			3	250300-1111	25-Mar-00	3	25-Mar	Mar
2e

Cell Formulas
Range	Formula
C1,K1,I1	C1	=ShowFilter(C4:C102)
G2	G2	=SUBTOTAL(3,G4:G102)
H4:H20	H4	=--TEXT(LEFT(G4,6),"00-00-00")
I4:I20	I4	=MONTH(H4)
J4:J20	J4	=DATE(1,MONTH(H4),DAY(H4))
K4:K20	K4	=LOOKUP(MONTH(H4),$L$3:$M$14)
F20	F20	=MONTH(H20)

The list used Advanced filter.
The Subtotal formula =SUBTOTAL(3,G4:G102) is a count of visible cells; it is custom formatted - 0 "is the Count"
The Filter criteria shows at the top. It uses a UDF. This may not be necessary since if you filter on 3 it is obvious the list shows results for March. The UDF is useful if you use multiple filters.
You can sort the list by date of Birth #2 (DOB2)

View with filter March; you could filter on column with month # instead.

T202009c.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	N/C= No Condition		N/C						N/C		=Mar
2							3 is the Count
3	Dato	Dag	Navn	Skibs nr.	Alder	31-12-20	CPR	DOB	MDR.	DOB2	Month
6	04		Anne		48	marts	040392-1111	4-Mar-92	3	04-Mar	Mar
7	14		Dorte		32	marts	290375-1111	29-Mar-75	3	29-Mar	Mar
20			XXX			3	250300-1111	25-Mar-00	3	25-Mar	Mar
21
2e

Cell Formulas
Range	Formula
C1,K1,I1	C1	=ShowFilter(C4:C102)
G2	G2	=SUBTOTAL(3,G4:G102)
H20,H6:H7	H6	=--TEXT(LEFT(G6,6),"00-00-00")
I20,I6:I7	I6	=MONTH(H6)
J20,J6:J7	J6	=DATE(1,MONTH(H6),DAY(H6))
K20,K6:K7	K6	=LOOKUP(MONTH(H6),$L$3:$M$14)
F20	F20	=MONTH(H20)

 

[Dave Patton]

The month abbreviated can be just=TEXT(H4,"mmm") or
not abbreviated =TEXT(H4,"mmmm")

 

[KlausW]

Hi Dave thank you very much for your help, I'm afraid I explained myself wrong. What I am looking for is shown in the picture I enclose. So I hope for your help. Regards Klaus W

 

[KlausW]

You can see that the months are in rows 2, 5, 8, etc. But as I also wrote, it varies from year to year. KW

 

[Dave Patton]

" You can see that the months are in rows 2, 5, 8, "

Why?

You can include the Months. N.B. You can centre across selection; this does not show in extract below.
Assign a DOB like 1-1-1
The sort on DOB2 will still work correctly.

Adding these rows will distort counts.

T202009c.xlsm
	A	B	C	D	E	F	G	H	I	J	K
3	Dato	Dag	Navn	Skibs nr.	Alder	31-12-20	CPR	DOB	MDR.	DOB2	Month
4	January							1-Jan-01		01-Jan	Jan
5	18		John		29	januar	200158-1111	20-Jan-58	1	20-Jan	Jan
6	February							1-Feb-01		01-Feb	Feb
7	20		Ole		62	februar	040272-1111	4-Feb-72	2	04-Feb	Feb
2e

Cell Formulas
Range	Formula
J4:J7	J4	=DATE(1,MONTH(H4),DAY(H4))
K4:K7	K4	=TEXT(H4,"mmm")
I5,I7	I5	=MONTH(H5)
H5,H7	H5	=--TEXT(LEFT(G5,6),"00-00-00")
H6	H6	=EDATE(H4,1)

 

[KlausW]

Thank you very much, I have try but I can not get Excel to do that you have make it do. I do not can figuer out what I am does wrong but I think it has something to do with filters.
it's just like in # 6 I wish it should look. Regards Klaus w

 

[Dave Patton]

1. I sorted based on column DOB2

T202009c.xlsm
	A	B	C	D	E	F	G	H	I	J	K
3	Dato	Dag	Navn	Skibs nr.	Alder	31-12-20	CPR	DOB	MDR.	DOB2	Month
4	January							1-Jan-01		01-Jan	Jan
5	18		John		29	januar	200158-1111	20-Jan-58	1	20-Jan	Jan
6	February							1-Feb-01		01-Feb	Feb
7	20		Ole		62	februar	040272-1111	4-Feb-72	2	04-Feb	Feb
8	March							1-Mar-01		01-Mar	Mar
9	04		Anne		48	marts	040392-1111	4-Mar-92	3	04-Mar	Mar
10			XXX			3	250300-1111	25-Mar-00	3	25-Mar	Mar
2e

Cell Formulas
Range	Formula
J4:J10	J4	=DATE(1,MONTH(H4),DAY(H4))
K4:K10	K4	=TEXT(H4,"mmm")
I5,I9:I10,I7	I5	=MONTH(H5)
H5,H9:H10,H7	H5	=--TEXT(LEFT(G5,6),"00-00-00")
H6,H8	H6	=EDATE(H4,1)
F10	F10	=MONTH(H10)

If I just wanted the information for Jan and Feb, I would select those months in the Months column.

T202009c.xlsm
	A	B	C	D	E	F	G	H	I	J	K
3	Dato	Dag	Navn	Skibs nr.	Alder	31-12-20	CPR	DOB	MDR.	DOB2	Month
4	January							1-Jan-01		01-Jan	Jan
5	18		John		29	januar	200158-1111	20-Jan-58	1	20-Jan	Jan
6	February							1-Feb-01		01-Feb	Feb
7	20		Ole		62	februar	040272-1111	4-Feb-72	2	04-Feb	Feb
2e

Cell Formulas
Range	Formula
J4:J7	J4	=DATE(1,MONTH(H4),DAY(H4))
K4:K7	K4	=TEXT(H4,"mmm")
I5,I7	I5	=MONTH(H5)
H5,H7	H5	=--TEXT(LEFT(G5,6),"00-00-00")
H6	H6	=EDATE(H4,1)

 

[KlausW]

I am sorry to say I cannot figuer out have to do, I do as you type, but it does not work. Will you help, I can send a link in Dropbox.

 

[Dave Patton]

Ok Send to dropbox and post a link.
I will look at the file.