+ Reply to Thread
Results 1 to 4 of 4

List of unique dates with multiple text entries

  1. #1
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    List of unique dates with multiple text entries

    I am creating a yearly calendar that can start from any month as specified by the user. The calendar for each month will show the previous month and the next month as well. I have a list of public holidays, but because the calendar can be started from any month I need to have 3 years of holiday dates, from December the year before the first month (in case the user chooses the start month of January so I can show the previous month when they are looking at the January month) right through to December of the year after the first month (in case the user chooses the start month of December so I can show the following month when they are looking at the November month). This is further complicated by the fact that some of the holidays are variable, so some years they will fall within the period and other years they wont, and some years two holidays will fall on the same date and in other years they will fall on different dates. What I am wanting is to get a unique list of the holiday dates that fall within the period of the 1st of the month prior to the month chosen to start the calendar right through to the end of the month, 13 months after the month chosen to start the calendar. I know how to find the date range using edate and eomonth functions, but I can't work out the best way to pull out only the dates within that range. I was thinking it would probably be an array formula, but I'm not too familiar with those, especially over multiple columns

    Once I have the unique list of dates I want to bring into an adjacent cell all of the names of the holidays that occur on that date, preferably with a comma and space separating them, or at least a space separating them (that is why each holiday name has a space at the end of it).

    Attached is a draft spreadsheet with the holidays in it, and a basic one month calendar. Any help is appreciated. This is my own creation so I can move things around if necessary.
    Attached Files Attached Files
    Please add reputation by clicking on the * if I have helped.
    Please mark the thread SOLVED if your issue has been resolved.
    Thanks, Glenn.

  2. #2
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: List of unique dates with multiple text entries

    Bumped hoping for a response. 40+ views, but no suggestions.

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: List of unique dates with multiple text entries

    I'll need to think more for multiple holidays but here's what I did, maybe it'll spark an idea.

    In A4 copied (formulas only) to the right and every other row downward

    =IFERROR(INDEX(Sheet2!$A$2:$A$25, MATCH(A3, INDEX(Sheet2!$C$2:$E$25, ,MATCH(YEAR($A$1),Sheet2!$C$1:$E$1)),0)),"")
    See attachment.
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: List of unique dates with multiple text entries

    Thanks ChemistB, but getting the names into the calendar I already knew how to do. It is the duplicate holidays that I cannot work out how to bring onto the calendar. The plan was also to have a month per tab with a linked picture for the last month and the next month in the top corners of each month's tab. That linked picture would be linked to a different tab that would have a full year shown. This would have cells too small to have the name of each holiday so I was going to colour them based on conditional formatting from the unique list of dates I originally asked for. I am open to other ways of doing things though. I have updated my example spreadsheet. It is the two highlighted sections in the Holidays tab I want the help with.
    Attached Files Attached Files

+ 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. Using VBA: I have a list with multiple entries.Need to reduce down to unique entries
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-25-2011, 03:19 PM
  2. Need to create unique list from list of multiple entries
    By Jeff in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-17-2005, 01:05 AM
  3. filter list of text for unique entries using formulea
    By Domenic in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-06-2005, 06:05 AM
  4. [SOLVED] filter list of text for unique entries using formulea
    By The Nephalim in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 AM
  5. [SOLVED] filter list of text for unique entries using formulea
    By The Nephalim in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  6. filter list of text for unique entries using formulea
    By The Nephalim in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  7. [SOLVED] filter list of text for unique entries using formulea
    By The Nephalim in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM
  8. filter list of text for unique entries using formulea
    By The Nephalim in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

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