Thanks Tyron,
Here is the formula in D11
Code:
[LEFT][COLOR=#000000][FONT=arial]=ArrayFormula(substitute(trim(transpose(split(concatenate({(if(unique(ArrayFormula(filter(TEXT(B:B,"dd/mm"),date(0,month(B:B),day(B:B))>date(0,month(E2),day(E2)),date(0,month(B:B),day(B:B))<=date(0,month(E2),day(E2)+E9))))=transpose(ArrayFormula(filter(TEXT(B:B,"dd/mm"),date(0,month(B:B),day(B:B))>date(0,month(E2),day(E2)),date(0,month(B:B),day(B:B))<=date(0,month(E2),day(E2)+E9)))),transpose((query({A:A,text(B:B,"dd/mm")},"select Col1 where '"&join(", ",unique((filter(TEXT(B:B,"dd/mm"),date(0,month(B:B),day(B:B))>date(0,month(E2),day(E2)),date(0,month(B:B),day(B:B))<=date(0,month(E2),day(E2)+E9)))))&"' contains Col2")) ),))&" ",transpose(split(rept(Char(10)&char(9),countunique((filter(TEXT(B:B,"dd/mm"),date(0,month(B:B),day(B:B))>date(0,month(E2),day(E2)),date(0,month(B:B),day(B:B))<=date(0,month(E2),day(E2)+E9))))),char(9)))}),char(10))))," ",", ")&" - "&unique((filter(TEXT(B:B,"dd/mm"),date(0,month(B:B),day(B:B))>date(0,month(E2),day(E2)),date(0,month(B:B),day(B:B))<=date(0,month(E2),day(E2)+E9)))))[/FONT][/COLOR][/LEFT]
Let me explain everything once again (i will try my level best, no sleep since last 24 hours, lots of work)
A2 to A2000 has names & B2 to B2000 has birthdates (i have this names and birthdates data with me, i will put it manually one by one or i can publish a spreadsheet form online for my users). So No formulas here in column A & B.
D2 - this cell has no formula, but we can input date here (this is just for checking sake, so whenever my sheet gets its final formula, i can check it with any date i wish)
E2 - this cell has formula, it checks if D2 has some date, if yes then it pulls d2 data, if d2 is empty, it shows current date by this formula
Code:
[LEFT][COLOR=#000000][FONT=arial]=if(D2="",today(),D2)[/FONT][/COLOR][/LEFT]
D6 - this cell has formula, it checks the whole list of dates in B2 to B2000 or can say whole column B, if any date matches with E2 (which is current date, or the date written by me in D2) then it pulls out name(s) of that user(s) and shows it with commas. For example if value in E2 is 16-11-2014 then D6 formula check this date with full column B, if it finds any match, it pulls the name in same row of column A and shows it here. Formula for D6 is
Code:
[LEFT][COLOR=#000000][FONT=arial]=iferror("Happy Birthday to "&join(", ",filter(A:A,month(B:B)=month( E2),day(B:B)=day(E2))))[/FONT][/COLOR][/LEFT]
But now, i have updated this formula with the one given by you, So now updated/rectified formula for D6 is...
Code:
=if(iferror("Happy Birthday to "&join(", ",filter(A:A,month(B:B)=month( E2),day(B:B)=day(E2))))=0,"No Member's Birthday Today",iferror("Happy Birthday to "&join(", ",filter(A:A,month(B:B)=month( E2),day(B:B)=day(E2)))))</pre>
E9 - This cell has no formula, its there just to put some figure (number of days) which can be used in formula of D11. For example if we enter number
"15" in cell E9, then the formula in D11 will sort list of
15 users upcoming birthdates (from current date or from date entered in D2). For example if D2 is empty, E2 will show current date, say its 16-11-2014, If we put number 7 in E9 then D11 formula searches for users/birth dates having birthday in upcoming 7 days and display list of users with dates in D11.
15 is really a large number, i am happy with 7 or 10. And yes, my idea about changing this to 15 users instead of 15 days wasnt a good idea. Let it remain as it is... infact i will reduce it to 7 days.
Now here comes the MONSTER
D11 - This cell has formula, infact most important formula for this sheet (which is currently not perfect).
Say if E2 has a date of 16-11-2014 & E9 has number 7. Then D11 formula should check/search next 7 days dates in column B and if it finds any match, it should pull username from same row (from column A) and should display something like this
17/11 - user3, user888, user 75. (these user's birthday is on 17/11, which is next day from current date (upcoming day))
18/11 - user22, user975, user 666, user 1091 (these user's have birthday on 18/11, which is day after tomorrow from current date.
20/11 - user99 (only one user birthday on 20/11, and no birthdays on 19/11
Above list can go till maximum 7 rows or less, because i have given range of 7 days in cell E9.
Now, One guy helped me in D11 formula, but formula is not complete, it has some errors, and this is the last pending thing. So the formula for D11 is
Code:
[LEFT][COLOR=#000000][FONT=arial]=ArrayFormula(substitute(trim(transpose(split(concatenate({(if(unique(ArrayFormula(filter(TEXT(B:B,"dd/mm"),date(0,month(B:B),day(B:B))>date(0,month(E2),day(E2)),date(0,month(B:B),day(B:B))<=date(0,month(E2),day(E2)+E9))))=transpose(ArrayFormula(filter(TEXT(B:B,"dd/mm"),date(0,month(B:B),day(B:B))>date(0,month(E2),day(E2)),date(0,month(B:B),day(B:B))<=date(0,month(E2),day(E2)+E9)))),transpose((query({A:A,text(B:B,"dd/mm")},"select Col1 where '"&join(", ",unique((filter(TEXT(B:B,"dd/mm"),date(0,month(B:B),day(B:B))>date(0,month(E2),day(E2)),date(0,month(B:B),day(B:B))<=date(0,month(E2),day(E2)+E9)))))&"' contains Col2")) ),))&" ",transpose(split(rept(Char(10)&char(9),countunique((filter(TEXT(B:B,"dd/mm"),date(0,month(B:B),day(B:B))>date(0,month(E2),day(E2)),date(0,month(B:B),day(B:B))<=date(0,month(E2),day(E2)+E9))))),char(9)))}),char(10))))," ",", ")&" - "&unique((filter(TEXT(B:B,"dd/mm"),date(0,month(B:B),day(B:B))>date(0,month(E2),day(E2)),date(0,month(B:B),day(B:B))<=date(0,month(E2),day(E2)+E9)))))[/FONT][/COLOR][/LEFT]
Above formula has some errors, it doesnt works if we enter year ending dates like 31-12-2014 in D2 and range of 7 days in E9. Pls fix this so it can show upcoming birthdays of users having birthday in upcoming 7 days (January)
Another error I found was, if we enter dates like 29-02-2014, D11 says
#N/A why so ! All other dates are working fine, then why animosity with february last day.
Check it live, Pls feel free to check and edit
this sheet, (This is just a sample copy, its not original, so feel free to edit)
2 more things i wish to change,
1. D11 should show date first and names afterwards (instead of names first and dates afterwards)
Like This
Code:
[COLOR=#222222][FONT=Arial]15-04 - user1, user2
[/FONT][/COLOR][COLOR=#222222][FONT=Arial]21-04 - user3, user4
[/FONT][/COLOR][COLOR=#222222][FONT=Arial]27-04 - user11, user6[/FONT][/COLOR], user88, user509
and not like this
Code:
[COLOR=#222222][FONT=Arial]user1, user2 - 15-04
[/FONT][/COLOR][COLOR=#222222][FONT=Arial]user3, user4 - 21-04
[/FONT][/COLOR][COLOR=#222222][FONT=Arial]user11, user6[/FONT][/COLOR], user88, user509 - 27-04
I think this will be easy for you, just changing positions (though i dont know, how to do it
)
2. Dates in cell D11 are not in order, Pls fix this. it should be like this
Code:
[COLOR=#222222][FONT=Arial]
15-04 - user1, user2
[/FONT][/COLOR][COLOR=#222222][FONT=Arial]21-04 - user3, user4
[/FONT][/COLOR][COLOR=#222222][FONT=Arial]27-04 - user11, user6[/FONT][/COLOR], user88, user509
and not like this
Code:
[COLOR=#222222][FONT=Arial]
21-04 - user3, user4
[COLOR=#222222][FONT=Arial]15-04 - user1, user2
[COLOR=#222222][FONT=Arial]27-04 - user11, user6[/FONT][/COLOR], user88, user509
[/FONT][/COLOR][/FONT][/COLOR]
Query & Explanation over.
Few suggestions (ideas) coming in my mind:
#2 would be easy i think, By anyway if we can sort column B by dates (ascending) (or by pulling and sorting it to column C). As far as i can understand, i think YEAR has no role in any formula, formula works only on mm and dd, so why not to sort only dd/mm in column C and then pull it to D11 formula. Well this is just a guess
Thanks for all your time and efforts.
Waiting ....
