Sorry if the title isn’t clear, but I need to take a schedule that’s on one sheet and break out the tasks by the people that are doing them. This is on a gdoc spreadsheet, and it’s pretty big, so lookups aren’t an option as they’re too heavy.
So does anyone have a lightweight way to take something like this:
TASK OWNER(S)
blow nose adam
go home adam, bob
make sandwich adam, chris
tie shoes bob
break bottles bob, chris
jump around chris
and on the other sheet spit out this:
adam blow nose go home make sandwich
bob go home tie shoes break bottles
chris make sandwich break bottles jump around
I can use iferror and search to ascertain whether the name appears in column B of first list, so I got that. What I can’t figure out is how to populate the tasks in the second table without using lookup.
It’s a gigantic pain to post spreadsheet stuff on here, so I’m just going to give you a high-level overview of what I’ve worked out. The key is to create an intermediary sheet where you have the tasks in the rows and the people in the columns. Then in each cell, if the person has the task, you concatenate the name of the task with the cell above it. So for your example, the first cell in Adam’s column (we’ll call this B2) would be “blow nose”. Then the formula in the cell below it (B3) would be something like =IF(ISERR(SEARCH(“adam”,Source!A2)),B2,B2 & “,go home”).
The idea here is to “bubble” the entire list down through the column so that at the bottom of the column you have your final list. If the list of tasks changes frequently, you can bubble the result up from the bottom as well. If you want the result tasks in separate cells, you can rig something up with FIND() and your delimiter (along with working position information in temporary cells) to do that.
Ok I’ve tried to make my solution at https://docs.google.com/spreadsheet/ccc?key=0AlN0lsSe-F9HdFlpQ015Ni1DTVRUZ1ZnUkVmQlBfTXc
The first sheet is the tasks, and the second is the task per person. I used a nested IF function for the second sheet:
=IF(Tasks!$B$2=“person 1”,“Task 1”,IF(Tasks!$C$2=“person 1”,“Task 1”,IF(Tasks!$D$2=“person 1”,“Task 1”,“0”)))
Which means:
IF person in first column of task 1 is person 1, display task 1
if not, IF person in second column…
if not, … display 0
But this method is tedious because you have to customise the formula for each cell.
I thought another method (if you don’t mind maths) would be to give each person a unique prime number. You calculate a code for each task by multiplying all the people’s numbers. By checking if a task’s code is divisible by a person’s number, you can tell if that person is in that task.
I tried to put this in the third sheet - task 1 is 266, which is only divisible by 2, 7 and 19, which means it involves people A, D and H. This won’t work if you have hundreds of people though.
You need to copy the three column block for each person, the tasks populate from the source data. That’s the only manual step. The darker blue is your final output, you can hide everything else. I also would fill the formulas down as far as possible, you need at least as many rows as you have tasks.
Like I mentioned earlier, this is best done with a Pivot Chart.
First, you need to clean up your data - that means just one piece of information per cell. No more “Adam, Chris” in one cell. It should look like this:
Task Person
blow nose Adam
go home Bob
go home Adam
make sandwich Adam
make sandwich Chris
tie shoes Bob
break bottles Bob
break bottles Chris
jump around Chris
Highlight the data, and select “Pivot Table report…” from the “Data” menu.
On the right side, click on “Rows - Add field”, and select “Person”.
Click on “Rows - Add field” again, and select “Task”.
Done. It should look like this:
Adam blow nose
go home
make sandwich
Adam Total
Bob break bottles
go home
tie shoes
Bob Total
Chris break bottles
jump around
make sandwich
Chris Total
Grand Total
I used a couple of my neater tricks in there. The running total - string concatenation thing is the Excel equivalent of a fold from functional programming. The way the data is displayed in the end is an example of something I call a “view formula,” where you start with how you want the data to look and make formulas that reference the position in the output (running position information, output cell references using ROW(), COLUMN() and the like, etc.) to make organized output out of disorganized data.