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.
"Lars-Ã…ke Aspelin" wrote:
> On Sat, 21 Jun 2008 03:40:00 -0700, Hugh Murfitt
> <(E-Mail Removed)> wrote:
>
> >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
>
>
>
Similar Threads
