CODEWITHSUNDEEP
arraysindexinggoogle-sheetslookup
Damián Gil
Damián Gil

Reputation: 43

match, index, and concatenate to just one cell

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.

Upvotes: 1

Views: 1119

Answers (1)

Ed Nelson
Ed Nelson

Reputation: 10259

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

Upvotes: 1

Related Questions