+ Reply to Thread
Results 1 to 18 of 18

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

  1. #1
    Registered User
    Join Date
    08-16-2013
    Location
    India
    MS-Off Ver
    Office 365 (Monthly Targeted Channel)
    Posts
    26

    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: copy to clipboard
    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: copy to clipboard
    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: copy to clipboard
    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 ...

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

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

    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

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

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

    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

  4. #4
    Registered User
    Join Date
    08-16-2013
    Location
    India
    MS-Off Ver
    Office 365 (Monthly Targeted Channel)
    Posts
    26

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

    @ 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.

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

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

    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

  6. #6
    Registered User
    Join Date
    08-16-2013
    Location
    India
    MS-Off Ver
    Office 365 (Monthly Targeted Channel)
    Posts
    26

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

    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

  7. #7
    Registered User
    Join Date
    08-16-2013
    Location
    India
    MS-Off Ver
    Office 365 (Monthly Targeted Channel)
    Posts
    26

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

    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.

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

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

    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

  9. #9
    Registered User
    Join Date
    08-16-2013
    Location
    India
    MS-Off Ver
    Office 365 (Monthly Targeted Channel)
    Posts
    26

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

    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.

  10. #10
    Registered User
    Join Date
    08-16-2013
    Location
    India
    MS-Off Ver
    Office 365 (Monthly Targeted Channel)
    Posts
    26

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

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

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

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

    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

  12. #12
    Registered User
    Join Date
    08-16-2013
    Location
    India
    MS-Off Ver
    Office 365 (Monthly Targeted Channel)
    Posts
    26

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

    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

  13. #13
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

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

    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
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    08-16-2013
    Location
    India
    MS-Off Ver
    Office 365 (Monthly Targeted Channel)
    Posts
    26

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

    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.

  15. #15
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

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

    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

  16. #16
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

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

    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

  17. #17
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

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

    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

  18. #18
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

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

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pull list of names if a value matches in row
    By GigaFluxx in forum Excel General
    Replies: 5
    Last Post: 11-19-2014, 03:58 PM
  2. [SOLVED] Search a list of names and automatically return any names not already included in table
    By bishbash89 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-21-2013, 09:06 AM
  3. Replies: 3
    Last Post: 02-05-2009, 03:56 PM
  4. [SOLVED] Transfer names and date of birth
    By payments in forum Excel General
    Replies: 1
    Last Post: 03-29-2006, 07:20 AM
  5. Replies: 3
    Last Post: 02-01-2005, 07:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1