Excel question - showing tasks by owner

General Questions
#1

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.

Thanks in advance if anyone can help.

#2

Sorry those lists look a mess.

#3

Can gdoc do pivot tables? That’d be the absolute easiest Excel solution.

#4

Would it be ok if you did something like
blow nose go home make sandwich
adam blow go make
bob - go -

?
aaah the board hides multiple spaces. I’ll fix this later.

#5

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.

#6

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.

#7

OK, here’s mine: https://docs.google.com/spreadsheet/ccc?key=0Ao5-oxImagTIdFp2TGxvc1h5dGFjdjAtQlFnVkxvR3c#gid=1

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.

#8

Your answer looks better than mine - at least less customisation is needed. I should study the functions you used.

#9

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
  1. Highlight the data, and select “Pivot Table report…” from the “Data” menu.
  2. On the right side, click on “Rows - Add field”, and select “Person”.
  3. 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
#10

Ha, I wish! No, the folks using the scheduler insist in inputting all the names in one cell with commas.

Anyway, thanks for all the insights, I’ve got something now that works pretty well. A little slow but bearable.

#11

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.