Birthday List

[Hugh Murfitt]

I have a table of kids details like this:
Columns A to K contain
Child_Surname, Child_Name_1, DOB_Child_1, Child_Name_2, DOB_Child_2,
Child_Name_3, DOB_Child_3, Child_Name_4, DOB_Child_4, Child_Name_5,
DOB_Child_5

I want to produce a table with four columns: Month (in moth order), day (in
date order), Child Name and Age (in age order).

Can anyone help? I've struggled with this for a long time but never get
quite what I want!!

 

[Don Guillett]

If desired, send your workbook to my address below along with a snippet of
this and before/after example(s)

 

[JLatham]

Hugh,
Is this just the one row of data, or are there a lot of rows containing
information on (up to) 5 children per row? Also, what is the row number with
the first child's name on it?
I'm out of town until Sunday evening, but with this information either
someone else can show how to transpose it and set things up to get the sorted
table the way you want, or I'll check when I return for a reply from you and
if no other help has been given, I'll jump back in.

Quick start: easy way to get a row of data into a column of data is to use
Copy to copy the row of information, pick a new spot and then use Edit |
Paste Special along with the [Transpose] option to turn the row of data into
a column of data.

 

[Lars-Åke Aspelin]

I have a table of kids details like this:
Columns A to K contain
Child_Surname, Child_Name_1, DOB_Child_1, Child_Name_2, DOB_Child_2,
Child_Name_3, DOB_Child_3, Child_Name_4, DOB_Child_4, Child_Name_5,
DOB_Child_5

I want to produce a table with four columns: Month (in moth order), day (in
date order), Child Name and Age (in age order).

Can anyone help? I've struggled with this for a long time but never get
quite what I want!!

Assuming that this is not a one time conversion of data structure but
that you still want the rows of families with up to 5 children per
family and later add data to this and, in parallel want to have a list
of all birthdays sorted per month, day etc that is automagically
updated, you can try this:

Your data is in columns A to K.
Set a limit on the number of families, e.g. 30, and then name the area
A1:K30 as Table.

Introduce 5 helper columns (L, M, N, O, and P) with the following
formulas in them from row 1 to row 150 (5 times the number of rows in
Table as there is a mximum of 5 chilren per family/row)

In L1:L150 you enter the following array formula:
(without any line breaks)
Array formulas must be entered by CTRL+SHIFT+ENTER rather than just
ENTER.

=IF(INDEX(Table,MOD(ROW(OFFSET(A1,0,0,ROWS(Table)*5,1))-1,
ROWS(Table))+1,3+2*INT((ROW(OFFSET(A1,0,0,ROWS(Table)*5,1))-1)/
ROWS(Table)))="","",MONTH(INDEX(Table,MOD(ROW(OFFSET(A1,0,0,
ROWS(Table)*5,1))-1,ROWS(Table))+1,3+2*INT((ROW(OFFSET(A1,0,0,
ROWS(Table)*5,1))-1)/ROWS(Table)))))

This should give you a column of months


In M1:M150 you enter the following array formula:

=IF(OFFSET(L1,0,0,ROWS(Table)*5,1)="","",DAY(INDEX(Table,MOD(
ROW(OFFSET(A1,0,0,ROWS(Table)*5,1))-1,ROWS(Table))+1,3+2*INT(
(ROW(OFFSET(A1,0,0,ROWS(Table)*5,1))-1)/ROWS(Table)))))

This should give you a column of days in month


In N1:N150 you enter the following array formula:

=IF(OFFSET(L1,0,0,ROWS(Table)*5,1)="","",YEAR(TODAY())-YEAR(
INDEX(Table,MOD(ROW(OFFSET(A1,0,0,ROWS(Table)*5,1))-1;ROWS(

Table))+1,3+2*INT((ROW(OFFSET(A1,0,0,ROWS(Table)*5,1))-1)/ROWS(Table)))))

This should give you a column of ages, the age that the respective
child will have this year,


In O1 you enter the following formula and copy it down to O150:

=IF(L1="","",ROW()+N1*1000+M1*100000+L1*10000000)

This should give you a column of unique values to be used for sorting.


In P1 you enter the following formula and copy it down to P150:

=MOD(SMALL(OFFSET(O$1,0,0,ROWS(Table)*5,1),ROW()),1000)

This should give you a column with row numbers sorted as needed


In Q1 you enter the following formula and copy it down to Q150:

=IF(ISERROR(INDEX(OFFSET(L$1,0,0,ROWS(Table)*5,1),P1)),"",INDEX(OFFSET(L$1,0,0,ROWS(Table)*5,1),P1))

This should be the same as column L, only sorted.


In R1 you enter the following formula and copy it down to R150:

=IF(ISERROR(INDEX(OFFSET(M$1,0,0,ROWS(Table)*5,1),P1)),"",INDEX(OFFSET(M$1,0,0,ROWS(Table)*5,1),P1))

This should be the same as column M, only sorted


In S1:S150 you enter the following array formula:

=IF(ISERROR(INDEX(OFFSET(L$1,0,0,ROWS(Table)*5,1);OFFSET(P1,0,0,ROWS(Table)*5,1))),"",INDEX(

Table,MOD(INDEX(OFFSET(P1,0,0,ROWS(Table)*5,1),ROW(OFFSET(S1,0,0,ROWS(Table)*5,1)))-1,ROWS(Table))+1,0))

This should give a column with the family name


In T1:T150 you enter the following array formula:

=IF(ISERROR(INDEX(OFFSET(L$1,0,0,ROWS(Table)*5,1);OFFSET(P1,0,0,ROWS(Table)*5,1))),"";INDEX(

Table,MOD(INDEX(OFFSET(P1,0,0,ROWS(Table)*5,1),ROW(OFFSET(T1,0,0,ROWS(Table)*5,1)))-1;ROWS(

Table))+1,2+2*INT((INDEX(OFFSET(P1,0,0,ROWS(Table)*5,1),ROW(OFFSET(T1,0,0,ROWS(Table)*5,1)))-1)/ROWS(Table))))

This should give a column of child first name


In U1 you enter the following formula and copy it down to U150:

=IF(ISERROR(INDEX(OFFSET(N$1,0,0,ROWS(Table)*5,1),P1)),"",INDEX(OFFSET(N$1,0,0,ROWS(Table)*5,1),P1))

This should be the same as column N, only sorted


Finally you can hide the helper columns L, M, N, O, and P.

Your sorted birthday table is now in columns and will be updated
whenever you make any changes in the table in columns A through K.

Q (month of birhtday)
R (day of birthday)
S (family name)
T (child name)
U (age the current year)

Hope this helps. / Lars-Åke

 

[Hugh Murfitt]

Thanks Lars (and Don and J), that looks really promising. I'll be working on
this again later today and will let you know how it goes.