JOIN / COLLECT Formula Assistance?

Jason Templer

Hello! I'm trying to setup a formula so that I can collect any names that are in columns "Worker #1 Name" through "Worker #10 Name".... and have them separated with a /

However, there are other columns in the middle of these (see screenshot) and the formula that I setup seems to be capturing everything within these columns - when I only want to capture the 'names' from the worker names columns...... any help? thanks!

=JOIN([Worker #1 Name]1:[Worker #10 Name]1, " / ")

join.JPG

Paul Newcome

It looks like your columns are set up to be [name/date/name/date/name/date.........] and you just want to skip over the dates. If that is correct, you could try something along the lines of...

=JOIN(COLLECT([Worker #1 Name]1:[Worker #10 Name]1, [Worker #1 Name]1:[Worker #10 Name]1, NOT(ISDATE(@cell))), " / ")

Jason Templer

Paul - this is perfect and works great! Only issue now is that it is showing the forward slash multiple times for the cells that have no names/dates - looks like this: Bob Wilson / Tom Thompson / / / / / / / / / / /

Is there something I can do to the formula so it doesn't include the forward slashes for blanks?

Thanks so much in advance, you're a lifesaver!

Paul Newcome

Sure thing. We can exclude blank names similar to how we excluded dates.

=JOIN(COLLECT([Worker #1 Name]1:[Worker #10 Name]1, [Worker #1 Name]1:[Worker #10 Name]1, AND(NOT(ISDATE(@cell)), @cell <> "")), " / ")

Jason Templer

Wow - THANK YOU Paul! You saved the day! :)

I have a few issues on this sheet but don't want to bother you with all of them... but one of them is that as you know I have columns where people will enter a Worker Name and then a Date in which that worker will need access to our laboratory. I'm having all of this info populate into a calendar (google) so they can see for each day of the month which workers will access the lab on which dates.

The problem is that my current setup is showing ALL workers and ALL dates on each row in the calendar, not separating them out by each date (i.e. if Worker #1 Name is Bob Smith and he is coming on 6/22/20, then Worker #2 is James Wilson and he is coming on 6/23/20 - the calendar is showing BOTH Bob Smith and James Wilson coming on dates 6/22 AND 6/23/10). Is it possible to setup a formula to separate these out by date/name? Not sure if that makes sense....?

I can share the sheet with you but not sure how to share or what email to share it with? Any help is SO much appreciated if you have time? Thank you again. Jason

Paul Newcome

What is the reason for having it structured the way you currently do?

Jason Templer

I have a form where people enter access requests and enter the name(s) for who wants access to our laboratory and the date(s) they want access. This is then routed to management for review and approval. The reason it is structured this way is that we want people submit a request for access but also allow them to enter multiple requests/dates on the same line.....sometimes the request is just for 1 person and 1 date, but sometimes it is multiple people on different dates...... I set it up this way so that it would properly feed into a calendar that would show for each day who has been approved for access.

Let me know if this make sense? I have a sheet I can share with you if you have time? Not sure how to share it here/securely?

Paul Newcome

I personally would suggest a restructure so that it is only one request per form. You can have the form set up to reload a "blank" once it is submitted to make it easier to fill out multiple requests quickly, but that is going to be MUCH easier than trying to restructure after the data has already been entered.

Jason Templer

Good idea. Problem is that management wants this to be this way unfortunately - but I may be able to convince them to change that. They wanted to have the ability for someone to submit a request and list a worker or possibly a few workers and list out the dates they need access. It would be much easier if it were only one worker allowed for each request - totally agree..... for now this is good and I will see how it goes. Thank you again!

One more: I'm trying to only have dates populated ONLY IF the status changes to 'Scheduled' - this is the formula that I came up with but its not working - any suggestions? The 'MIN' is because this is in a column that only collects the lowest/oldest date.....

=IF([Scheduling Complete/Added to Schedule?] = "Scheduled"),=MIN([Date1]@row:[Date10]@row))

Paul Newcome

Try this instead...

=IF([Scheduling Complete/Added to Schedule?]@row = "Scheduled", MIN([Date1]@row:[Date10]@row))

I added the @row reference to the column name, removed the closing parenthesis in the middle, and removed the = from the middle.

If that doesn't work, we can make a tweak to the MIN function to only pull date values.

Jason Templer

Excellent - thanks! I messed around last night with this one for a long time and couldn't get it to work for me. You've been a great help Paul, thanks again so much!! Have a great day.

Paul Newcome

Happy to help! 👍️