• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Struggling to Produce concatenated names based on a date search

C

cma407

New Member
Hi,
I have a simple schedule file with Dates as Column Headers (e.g. B1:G1); and Team member in Column A (from A2 downwards). The table data indicates if that team member is doing a late start ("late"), early start ("early"), Does not work ("DNW"), or is away from the Office ("Away") that day.

My team has a daily briefing meeting, and I'd like this data to pre-propulate concatenation of the team members names against each of the categories (as shown in my uploaded file).

I'd be grateful for any assistance. I am not trained, nor confident with VBA. Also, the search and resulting concatenated data needs to appear on a different worksheet (which has been formatted as a meeting agenda template (Not ideal, but I don't have authority to change the way my workplace wants this.
 

Attachments

  • Schedule_Example_File.xlsx
    50.1 KB · Views: 13
Hi,

as it first depends on which Excel version do you have ?

The criteria cell E15 must have a valid date like the ones in row #1,
not a text like in your attachment …
​
 
I'm using a PC with Windows 10, Office 2016 at work Marc. (Sorry - the Mac is home use). Any suggestions for Windows?
 

OK my versions are too old, but I'm sure your version has a function
to concatenate the text from an array (like a range) named TEXTJOIN,
to see in Excel inner help …
​
 
Hi,

Since you have version 2016, like Marc suggested you can use textjoin.
I've worked on an alternative, but it requires some helper columns. You can hide them. File attached.
 

Attachments

  • Schedule_Example_File.xlsx
    17.5 KB · Views: 8
I'm a bit in awe at what you guys can achieve - thanks so much for this solution - I'm sure I can nut through it and get something workable out of it. I'm very grateful for your time.
 
Hi Marc, Thank you for your time and recommendations. I'm confident between your reply and GraH Guido's solution that I'll be able to make this work. Thanks for your expertise!!
 
Hi GraH Guido, Can I trouble you to share with me how to get the date search cell to look up later dates, e.g. If we delete historical data columns, how do I get it to always display five working days forward?
 
Hi cma407,
Sorry, I must have missed an alert and only noticed you extra question yesterday evening.
I assumed the dates would always be in the range B1:F1 on sheet 1.
But what you desire can be achieved as well.
I hope I understood correctly you want a list of dates, starting from today and 5 working days forward. That list of dates should be in the drop down. Take a look in the attached file. Would it be helpful like that?
 

Attachments

  • Copy of Schedule_Example_File.xlsx
    16.1 KB · Views: 13
GraH - Guido said:
Hi cma407,
Sorry, I must have missed an alert and only noticed you extra question yesterday evening.
I assumed the dates would always be in the range B1:F1 on sheet 1.
But what you desire can be achieved as well.
I hope I understood correctly you want a list of dates, starting from today and 5 working days forward. That list of dates should be in the drop down. Take a look in the attached file. Would it be helpful like that?
Click to expand...
GraH - Guido said:
Hi cma407,
Sorry, I must have missed an alert and only noticed you extra question yesterday evening.
I assumed the dates would always be in the range B1:F1 on sheet 1.
But what you desire can be achieved as well.
I hope I understood correctly you want a list of dates, starting from today and 5 working days forward. That list of dates should be in the drop down. Take a look in the attached file. Would it be helpful like that?
Click to expand...
 
You must log in or register to reply here.
Back
Top