1

I have a Google spreadsheet with two sheets, that I want to use to plan the calendar for my staff. One sheet is called "maquinas" (machines). The rows are the dates and the columns are the machines. Inside each cell there's the name of the operator of the machine. Like this:

machines sheet

Note that:

-some machines are inoperative some days (marked in black)

-there are special days when a machine that normally is operative has to close (cell H5)

-some operators have to operate several machines the same day

I have another sheet called "personas" (employees), where I want that each employee only has to look to one cell to know everything he has to do on each day (a list of all the machines that he must operate that day). This is an example of the desired result:

employees

The order in which the machines appear in each cell is not important, as long as every one of them appears.

I have no idea about how to solve it. I have tried to bypass it creating a huge "tridimensional spreadsheet", with dates in the rows, employees in the columns, and machines in the sheets (in the third dimension), and concatenate towards the first sheet. It works, but then is very cumbersome and error-prone to make changes in the employees' daily work.

I have a bad feeling. Probably it will need code or array formulas, and the function concatenate doesn't work with arrays. And I have no idea of how to code in VBA, much less in Google Spreadsheets.

Rubén
  • 34,714
  • 9
  • 70
  • 166
Damián Gil
  • 43
  • 7
  • IMHO, you should do divide the problem into smaller parts to make easy to build the solution. The first is to convert the matrix form of the data to simple table form ( "reverse pivot", normalize data). I posted a way to do this by using formulas as an [answer](http://stackoverflow.com/a/36123875/1595451) to http://stackoverflow.com/questions/24954722/how-do-you-create-a-reverse-pivot-in-google-sheets . – Rubén Oct 21 '16 at 18:11

1 Answers1

1

I think I have something you can try. Manually set up on your "personas" sheet, enter the employee names in row 1 and the days in column A. Just as they are on your example. Then paste this formula in cell B2:

=iferror(join(" ",(query(transpose(QUERY(maquinas!$B$1:$M$5 , "select * " )),"select Col1 where Col"&index(row())&" contains'"&B$1&"'"))))

Then drag the formula in B2 down as far as you need for days (If days are not yet entered, they will show as blank.) Then highlight all the all the cells with formulas in column B and drag it right to column J.

The "maquinas" is fine as is.

Let me know if you have a problem or need anything explained.

Here is a link to my working test spreadsheet. Make a copy and try it. If you are using a different language, you may need to change the , to ; in the formulas.

https://docs.google.com/spreadsheets/d/1jqDkYTy3rssqeKGJyLYzcMJ27c5X3a1P5osKkWPNOoM/edit?usp=sharing
Ed Nelson
  • 10,015
  • 2
  • 27
  • 29
  • I have modified the answer and added a link to my working test spreadsheet. – Ed Nelson Oct 24 '16 at 12:05
  • It works! Only it's very slow, because the real spreadsheet has 365 days in the rows. But it works! Now I'll try to dissect the formula to understand it... Thanks. – Damián Gil Oct 24 '16 at 15:57
  • I added a tab to the shared spreadsheet with an explaining the formula. I hope it helps. – Ed Nelson Oct 24 '16 at 18:07