From A Table Of Names And Birth Dates Pull A List Of Names As Of A Reference Date And Ther

I have posted this question before in google groups forum, and got half-solution. Pls have a look at this link to get an idea.

http://yogi--anand-consulting.blogsp...rth-dates.html

The solution was almost achieved, but the function posted by Mr. Yogi (the guy provided half solution) has some errors and now Mr. Yogi is unreachable.

Let me explain it once again.

Things i wish to achieve : I have a list of members and their birthdates, i am willing to enter this data manually. I am trying to create a sheet which can wish "happy birthday" to members having birthday today (current date) and a list of members having birthday in next 7 days (from current date). I can publish this sheet (particular cell range) on my webpage for the very purpose.

Have a look at sample sheet at below link, Feel free to edit but first make a copy of it so others can also have their say.
https://docs.google.com/spreadsheets...it?usp=sharing

Incase you are not getting it, Let me explain everything once again

A1 & B1 has headings called NAMES & BIRTHDATES respectively.

A2 to A2000 has names (i.e user1, user2, user3 and so on)

& B2 to B2000 has birthdates in DD/MM/YYYY format (i.e 13-12-1980, 25-05-1990, 31-2-1984) years can be anywhere between 1950 to year 2000.

(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

Formula:

Please Login or Register  to view this content.

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. And if no matches found, it shows "No Members Birthday Today". Formula for D6 is

Formula:

Please Login or Register  to view this content.

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 "7" in cell E9, then the formula in D11 will sort list of users having birthdays in upcoming 7 days (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 (17-11-2014 to 23-11-2014) and display list of users with dates in D11.

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 (this user birthday on 20/11)

Above list can go till maximum 7 rows or less, because i have given range of 7 days in cell E9.

Now, Mr.Yogi helped me in D11 formula, but formula is not complete, it has some errors, Formula for D11 is

Formula:

Please Login or Register  to view this content.

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.

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

15-04 - user1, user2
21-04 - user3, user4
27-04 - user11, user6, user88, user509


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

15-04 - user1, user2
21-04 - user3, user4
27-04 - user11, user6, user88, user509

and not like this

21-04 - user3, user4
15-04 - user1, user2
27-04 - user11, user6, user88, user509


Query & Explanation over.

Few suggestions (ideas) coming in my mind:
#2 would be easy i think, I dont know how to do it, But 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 (month) and dd (date), 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 ...

I don't use Google Docs, so I saved the file in xlsx format to my hard disc. When I opened it in Excel I noticed that many of your dates are not true dates - instead they are text values which just look like dates. I can change them using this formula in C2:

=IF(ISNUMBER(B2),B2,DATEVALUE(B2))

and then fix those values and paste them back into column B, but I wonder if Google Docs makes the distinction between true dates and text-dates?

I also wonder why you specifically want the matching names in one cell. Excel is not very good at multiple conditional concatenation, and so it will be easier to do this on successive rows, so that means using helper columns - will that be acceptable to you? (The helper columns can be hidden from view). If you do want just one formula, how many names (maximum) would you expect to have a birthday on any one day?

I presume if I supply you with an Excel file that you will be able to import this into Google? I know some functions do not translate directly.

Hope this helps.

Pete

By the way, 2014 was not a leap year, so 29/02/2014 did not exist - this is then taken as a text value, and thus breaks the formula. Mind you, the formulae did not come through into my Excel version, just the values.

Pete

@ Pete_UK I am planning to publish certain cells of this sheet on my webpage, so it doesnt matters if helper columns are there.

I cant predict the Number of users having birthday on any one day, it can be 1 or 100, but not more than 1000

I dont know if importing excel sheet in google docs will leave its functions intact. Formula is almost done, just few errors are there. And yes, it was a mistake 2014 is not a leap year. Thanks for the input.

Okay, to start off with, see if these formulae work for you in Google Docs:

Put this formula in G2:

=IF(DATE(YEAR($E$2),MONTH(B2),DAY(B2))=$E$2,COUNTIF(G$1:G1,">0")+1,"-")

and this one in H2:

=H1&IF(G2="-","",$A2&", ")

and copy both down to row 2000. Then put this formula in D6:

=IF(COUNT(G:G)=0,"No Member's Birthday Today","Happy Birthday to: "&SUBSTITUTE(LOOKUP("zzz",H:H)&",",", ,",""))

Put a few different dates in D2 to see the effect. Does this give you the correct names for the chosen dates?

Hope this helps.

Pete

As per your wish i can apply these functions and will post the results, No issues, but just to inform you... I have posted a link to Google Spreadsheet ... You can make a copy of that file in your google drive and can make any changes you wish. I mean it will be easy for you to check if formula works better

After applying given formula in G2, H2 and D6 here are the results. Date i entered in D6 is 12/31/2014 (mm/dd/yyyy format) though i need my sheet in dd/mm/yyyy format

Column G has "-" all over except G65 has "1" instead of "-" and from here H started displaying 1 name, scrolling down .. H shows another name along with the first name and then number of name increases as i scroll down. Above H65 column H remain blank.

Okay, put 2nd January 2000 in D2 - do you get this result in D6?:

Happy Birthday to: Nikita Crist, Cleta Wiza, Dolores Pfannerstill, Oren Schamberger, Jenifer Collier, Trenton O'Hara

If not, then you will need to change your dates as advised in Post #2.

I hear what you say about the link to the Google sheet, but I don't know what functions will work and don't know enough about it to know what the alternatives are.

Pete

Okay, Yes the function is working for D6, whenever G2 detects the same date in A column it gives that cell a number 1 and on another match found it gives it a number 2 and so on, Along with number names starts appearing in H column. And final results on D6 are fine. He He Now its turn for D11 the monster.

Yes, If i put date 1/2/2000 in D2 then it shows the same name posted by you

Well I had been thinking of doing a similar thing in columns I and J, but using the date range for column I as defined by E9, i.e. in I2:

=IF(AND(DATE(YEAR($E$2),MONTH(B2),DAY(B2))>$E$2,DATE(YEAR($E$2),MONTH(B2),DAY(B2))<=$E$2+$E$9),COUNTIF(I$1:I1,">0")+1,"-")

However, that won't give you the breakdown into successive days that you were asking for. If 7 is a typical value for E9, then you could have 7 pairs of helpers, one for each day, along the lines of the other two formuale, though that seems a bit OTT. Perhaps you could use the mmdd as a qualifier, with a unique reference following this, like:

=IF(AND(DATE(YEAR($E$2),MONTH(B2),DAY(B2))>$E$2,DATE(YEAR($E$2),MONTH(B2),DAY(B2))<=$E$2+$E$9),TEXT(B2,"mmdd")&"_"&COUNTIF(I$1:I1,TEXT(B2,"mmdd")&"*")+1,"-")

in I2 and then use INDEX/MATCH to pick each one and join them together.

It's getting a bit late here so I need to go to bed. I'll pick it up in the morning if I have any further thoughts.

Pete

Thanks for all your time PETE, I am not that good in excel formulas, can understand only layman's tounge. However all i understood is you are suggesting a formula for I2 and will carry on tomorrow. I will be waiting

Okay, put this formula in I2:

=IF(AND(DATE(YEAR($E$2),MONTH(B2),DAY(B2))>$E$2,DATE(YEAR($E$2),MONTH(B2),DAY(B2))<=$E$2+$E$9),TEXT(B2,"mmdd")&"_"&COUNTIF(I$1:I1,TEXT(B2,"mmdd")&"*")+1,"-")

and copy down to the bottom of your data. Then put this formula in D11:

=IF(ROWS($1:1)>$E$9,"",TEXT($E$2+ROWS($1:1),"dd-mm")&" - "&IF(ISNA(MATCH(TEXT($E$2+ROWS($1:1),"mmdd")&"*",I:I,0)),"none",INDEX(A:A,MATCH(TEXT($E$2+ROWS($1:1),"mmdd")&"_1",I:I,0))&IFERROR(", "&INDEX(A:A,MATCH(TEXT($E$2+ROWS($1:1),"mmdd")&"_2",I:I,0)),"")&IFERROR(", "&INDEX(A:A,MATCH(TEXT($E$2+ROWS($1:1),"mmdd")&"_3",I:I,0)),"")&IFERROR(", "&INDEX(A:A,MATCH(TEXT($E$2+ROWS($1:1),"mmdd")&"_4",I:I,0)),"")&IFERROR(", "&INDEX(A:A,MATCH(TEXT($E$2+ROWS($1:1),"mmdd")&"_5",I:I,0)),"")&IFERROR(", "&INDEX(A:A,MATCH(TEXT($E$2+ROWS($1:1),"mmdd")&"_6",I:I,0)),"")&IFERROR(", "&INDEX(A:A,MATCH(TEXT($E$2+ROWS($1:1),"mmdd")&"_7",I:I,0)),"")&IFERROR(", "&INDEX(A:A,MATCH(TEXT($E$2+ROWS($1:1),"mmdd")&"_8",I:I,0)),"")&IFERROR(", "&INDEX(A:A,MATCH(TEXT($E$2+ROWS($1:1),"mmdd")&"_9",I:I,0)),"")))

and copy down as far as you like. This will give you up to 9 names per day - if you want more, then you will need to duplicate the part of the formula shown in red above, and just change the 9 to 10, 11, 12 etc. for successive terms.

I've attached my Excel workbook - perhaps you can just import it into Google.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Hope this helps.

Pete

Well PETE, i did as you said, Extended D11 formula till number 51 (really large formula).

Now when i put date like 31st december 2014 in D2, D11 shows this...

01-01 - none
02-01 - none
03-01 - none
04-01 - none
05-01 - none
06-01 - none
07-01 - none

When i put date 1st january 2015 in D2, D6 says...

Happy Birthday to: Elva Strosin, Alexandrea Hodkiewicz PhD

and D11 says

02-01 - Nikita Crist, Cleta Wiza, Dolores Pfannerstill, Oren Schamberger, Jenifer Collier, Trenton O'Hara
03-01 - Emmanuelle Miller, Reinhold Rogahn, Ali Hermiston, Wellington Funk, Mr. Beulah Walter
04-01 - Taya Sporer, Dr. Earnest McKenzie, Vincent Little, Barrett Frami, Miss Ricardo Auer
05-01 - Jeanie Huels, Alexzander Bernier, Abner Kuvalis, Berta Batz, Laurine Barrows, Zachery Nienow I
06-01 - Cleve Wilderman, Tobin Hauck
07-01 - Avis Quitzon, Jaylin Collier, Peter Hettinger, Miller Heathcote, Roselyn Mitchell
08-01 - Chadrick Morissette, Stanley Sawayn MD, Kayli Hickle, Pansy Sipes, Hattie McDermott

So this is the error. This was there in my previous solution too. God knows when i will get rid of this.

Change the formula in I2 to this:

=IF(AND(DATE(YEAR($E$2+$E$9),MONTH(B2),DAY(B2))>$E$2,DATE(YEAR($E$2+$E$9),MONTH(B2),DAY(B2))<=$E$2+$E$9),TEXT(B2,"mmdd")&"_"&COUNTIF(I$1:I1,TEXT(B2,"mmdd")&"*")+1,"-")

(changes shown in red), then copy down to the bottom of your data - this should correct things when your dates are close to year-end.

Hope this helps.

Pete

Sorry, that still doesn't work if the chosen date is within (E9 - 1) days from the end of the year. Mind you, it's just the formula in I2 that needs tweaking, rather than the monster formulae in column D, so it's just a matter of adjusting the right parameter(s). I'll keep on digging after I've had something to eat.

Pete

Well, this one seems to work in I2:

=IF(OR(DATE(YEAR($E$2),MONTH(B2),DAY(B2))>$E$2,DATE(YEAR($E$2+$E$9),MONTH(B2),DAY(B2))<=$E$2+$E$9),TEXT(B2,"mmdd")&"_"&COUNTIF(I$1:I1,TEXT(B2,"mmdd")&"*")+1,"-")

Copy this down to the bottom of your data, and test it out by putting some different dates in D2 and by varying the days in E9.

Hope this helps.

Pete

Actually, here is a much simpler version of the formula for I2:

=IF(B2="","-",TEXT(B2,"mmdd")&"_"&COUNTIF(I$1:I1,TEXT(B2,"mmdd")&"*")+1)

this lists all month-day combinations with unique counts, so you could apply a filter to this column to see how many records you can expect for each day. (The maximum for any one day is 15, and there is another day which has 13, then a few with 12, so I think having up to 51 in the "monster" formula is a bit of overkill).

Hope this helps.

Pete