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

 

[KlausW]

Thanks

Birthday

Shared with Dropbox

www.dropbox.com

 

[Dave Patton]

I deleted Coloumn F; there was no header and the data seemed redundant.

I inserted a new row 4.
c4 text - month - Enter the Month label where you want it and center it across relevant columns.
DOB 01-01-01
I inserted new rows for the start of each month
DOB edate

Birthday.xlsx
	A	B	C	D	E	F	G	H	I	J
2
3	Dato	Dag	Navn	Skibs nr.	Alder	CPR	DOB	MDR.	DOB2	Month
4	JANUARY		- Month-				01.Jan.01	1	01-Jan	January
5	January		John			180191-1111	18.Jan.91	1	18-Jan	January
6	FEBUARY		- Month-				01.Feb.01	2	01-Feb	February
7	February		Anne			040272-1111	04.Feb.72	2	04-Feb	February
8	February		Dorte			140288-1111	14.Feb.88	2	14-Feb	February
9	MARCH		- Month-				04.Mar.72	3	04-Mar	March
10	March		Claus			040392-1111	04.Mar.92	3	04-Mar	March
Birthday list

Cell Formulas
Range	Formula
H4:H10	H4	=MONTH(G4)
I4:I10	I4	=DATE(1,MONTH(G4),DAY(G4))
J4:J10	J4	=TEXT(G4,"mmmm")
A5,A10,A7:A8	A5	=(TEXT(J5,"dd"))
C5,C10,C7:C8	C5	=IFERROR(VLOOKUP(F5,Name!$A$2:$B$25,2,FALSE),"")
F5	F5	=Name!A2
G5,G10,G7:G8	G5	=--TEXT(LEFT(F5,6),"00-00-00")
G6,G9	G6	=EDATE(G4,1)
F7:F8	F7	=Name!A3
F10	F10	=Name!A5

 

[Dave Patton]

I noticed your formula for the label and the columns that you centered across.
I revised the post to below but was too late to complete the edit to the post above.

Birthday.xlsx
	A	B	C	D	E	F	G	H	I	J
3	Dato	Dag	Navn	Skibs nr.	Alder	CPR	DOB	MDR.	DOB2	Month
4	January						01.Jan.01	1	01-Jan	January
5	January		John			180191-1111	18.Jan.91	1	18-Jan	January
6	February						01.Feb.01	2	01-Feb	February
7	February		Anne			040272-1111	04.Feb.72	2	04-Feb	February
8	February		Dorte			140288-1111	14.Feb.88	2	14-Feb	February
9	March						04.Mar.72	3	04-Mar	March
10	March		Claus			040392-1111	04.Mar.92	3	04-Mar	March
Birthday list

Cell Formulas
Range	Formula
H4:H10	H4	=MONTH(G4)
I4:I10	I4	=DATE(1,MONTH(G4),DAY(G4))
J4:J10	J4	=TEXT(G4,"mmmm")
C5,C10,C7:C8	C5	=IFERROR(VLOOKUP(F5,Name!$A$2:$B$25,2,FALSE),"")
F5	F5	=Name!A2
G5,G10,G7:G8	G5	=--TEXT(LEFT(F5,6),"00-00-00")
G6,G9	G6	=EDATE(G4,1)
F7:F8	F7	=Name!A3
A4,A9,A6	A4	=PROPER(TEXT(G4,"mmmm"))
A5,A10,A7:A8	A5	=(TEXT(J5,"dd"))
F10	F10	=Name!A5

 

[Sulprobil]

I suggest to use a small VBA macro:

Function sbBirthdayList(r As Range) As Variant
'Create monthly birthday list.
'Reverse("moc.LiborPlus.www") V0.10 PB 15-Sep-2010
Dim vR(1 To 13, 1 To 3) As Variant
Dim i As Long, j As Long
Dim sNames(101 To 1231) As String

'Fill temporary array
For i = 1 To r.Rows.Count
    If IsDate(r.Cells(i, 2)) Then
        j = Month(r.Cells(i, 2))
        vR(j + 1, 2) = vR(j + 1, 2) + 1 'Increasing DOB counter for month
        j = j * 100 + Day(r.Cells(i, 2))
        If sNames(j) <> "" Then sNames(j) = sNames(j) & ", "
        sNames(j) = sNames(j) & r.Cells(i, 1)
    End If
Next i

'Fill output area
vR(1, 1) = "Month"
vR(1, 2) = "#"
vR(1, 3) = "(Day) Names"
For i = 1 To 12
    vR(i + 1, 1) = Format(DateSerial(1900, i, 1), "MMMM")
    vR(i + 1, 3) = ""
    For j = 1 To 31
        If sNames(i * 100 + j) <> "" Then
            If vR(i + 1, 3) <> "" Then vR(i + 1, 3) = vR(i + 1, 3) & ", "
            vR(i + 1, 3) = vR(i + 1, 3) & "(" & j & ") " & sNames(i * 100 + j)
        End If
    Next j
Next i

sbBirthdayList = vR

End Function

Article sbBirthdayList

 

[KlausW]

Good morning Sulprobil
Thanks, I tried to insert the code into the sheets, I can get it does not make it run the code. Neither in the sheet nor in a module. What am I doing wrong. KW

 

[KlausW]

I noticed your formula for the label and the columns that you centered across.
I revised the post to below but was too late to complete the edit to the post above.

Birthday.xlsx
	A	B	C	D	E	F	G	H	I	J
3	Dato	Dag	Navn	Skibs nr.	Alder	CPR	DOB	MDR.	DOB2	Month
4	January						01.Jan.01	1	01-Jan	January
5	January		John			180191-1111	18.Jan.91	1	18-Jan	January
6	February						01.Feb.01	2	01-Feb	February
7	February		Anne			040272-1111	04.Feb.72	2	04-Feb	February
8	February		Dorte			140288-1111	14.Feb.88	2	14-Feb	February
9	March						04.Mar.72	3	04-Mar	March
10	March		Claus			040392-1111	04.Mar.92	3	04-Mar	March
Birthday list

Cell Formulas
Range	Formula
H4:H10	H4	=MONTH(G4)
I4:I10	I4	=DATE(1,MONTH(G4),DAY(G4))
J4:J10	J4	=TEXT(G4,"mmmm")
C5,C10,C7:C8	C5	=IFERROR(VLOOKUP(F5,Name!$A$2:$B$25,2,FALSE),"")
F5	F5	=Name!A2
G5,G10,G7:G8	G5	=--TEXT(LEFT(F5,6),"00-00-00")
G6,G9	G6	=EDATE(G4,1)
F7:F8	F7	=Name!A3
A4,A9,A6	A4	=PROPER(TEXT(G4,"mmmm"))
A5,A10,A7:A8	A5	=(TEXT(J5,"dd"))
F10	F10	=Name!A5

Good morning Dave I do not know how to for Excel to what I can not. Could you grab the file and open it from Dropbox?
KW

 

[Sulprobil]

Good morning Sulprobil
Thanks, I tried to insert the code into the sheets, I can get it does not make it run the code. Neither in the sheet nor in a module. What am I doing wrong. KW

Please click on the article link below the code. You can download a sample file at the end of that page.

 

[KlausW]

Thanks, I'm not a shark in Excel. So therefore these questions. I can not quite find the file you are writing about. I can find the Download folder. here I can donate to a hospital. That's the point of getting the file, isn't it? many thanks Klaus W.

 

[Sulprobil]

No. Underneath my code here is the link to my article, and directly below the same code of that article is the download link to a sample file.

 

[Fluff]

@Sulprobil
Maybe it would be helpful if you actually explained to the OP how to use your function, rather than trying to push him into downloading a file from a strange site and then having to try & figure out what is going on.