Organizing data in sheets

ABoado

Hello Smartsheet Community!

I am working on a project where I need to schedule staff into various sessions on different dates. I have set up a form and have collected this data. I listed each date as a question so I have 10 form fields with dates as column headers (Thursday, March 2) and staff choose either available or not available.

Now that I have this data, I realize that I am at a loss on how to figure out who is available on each date without manually sorting it. I originally did not set up my form with a form field with multiselect because I was worried about the data being jumbled but now I am wondering if I took the wrong approach. The process to figure out what sessions to assign to which available staff is becoming very manual and I'm wondering if anyone here has done a similar project and has any tips. Happy to screenshot my data if that is helpful.

Essentially, I want to assign staff to different dates based on their availability but there is a limit of the amount of staff for each date. Any ideas?

Jeff Reisman

@ABoado Here's an idea:

I am assuming your sheet is a grid with a column for the staff name, and then columns for each date, with Available or Unavailable listed in each date column for each staff name, yes?

Make a copy of your sheet, and then delete all the data except the staff names, but don't delete the columns. I'll call this Sheet 2.

Start on the top row in the first date column. Use an INDEX/COLLECT to pull in the staff name from the main sheet if that cell equals "Available", creating the references to the main sheet as you go:

=INDEX(COLLECT({Main Sheet Name Column}, {Main Sheet Name Column}, [Staff Name]@row, {Main Sheet March 2 column}, "Available"), 1)

The logic here says: Give me the value from the main sheet staff name column, where the staff name is the same as the staff name on this row, and where the March 2 column equals "Available".

Copy that formula down the March 2 column on Sheet 2.

Copy the formula into the next date column, and just replace the date column range from the main sheet with the next date column. Copy that formula down that date column in Sheet 2, and so on.

When you're done, you'll have a sheet where each date column contain the names of those available on that date. If so desired, at the bottom of each column you could JOIN the values together into one cell.

Jeff Reisman

@ABoado Here's an idea:

I am assuming your sheet is a grid with a column for the staff name, and then columns for each date, with Available or Unavailable listed in each date column for each staff name, yes?

Make a copy of your sheet, and then delete all the data except the staff names, but don't delete the columns. I'll call this Sheet 2.

Start on the top row in the first date column. Use an INDEX/COLLECT to pull in the staff name from the main sheet if that cell equals "Available", creating the references to the main sheet as you go:

=INDEX(COLLECT({Main Sheet Name Column}, {Main Sheet Name Column}, [Staff Name]@row, {Main Sheet March 2 column}, "Available"), 1)

The logic here says: Give me the value from the main sheet staff name column, where the staff name is the same as the staff name on this row, and where the March 2 column equals "Available".

Copy that formula down the March 2 column on Sheet 2.

Copy the formula into the next date column, and just replace the date column range from the main sheet with the next date column. Copy that formula down that date column in Sheet 2, and so on.

When you're done, you'll have a sheet where each date column contain the names of those available on that date. If so desired, at the bottom of each column you could JOIN the values together into one cell.

ABoado

Thank you so much Jeff!! Your idea worked great! I am so appreciative and grateful for your help!!