Friday, March 16, 2012

yogi_Split Columns Of Assignments By Name And Day Into Columns Of Names By Assignment

Yogi Anand, D.Eng, P.E.                Google Spreadsheet          Post  #471

user ppetak said: 
Hi all, 
i have a little problem - I have two columns, in one names of people, in other their assignment in one line with comma as delimiter. What I need is the other view on the problem - in first column all assignments, and in other all people names in one line. like: 
name| assignment_monday         | aasignment_tuesday | .... 
Joe | cooking, dishes, shopping | cooking, playing | ... 
Bob | cooking, playing, shopping| .... 
Me  | playing                   | .... 
and result: 
assign  | monday   | tuesday 
cooking | Joe, Bob | Joe ... 
dishes  | Joe 
shopping| Joe, Bob 
playing | Bob, Me 
I have found two solutions, one is using arrayformula: =arrayformula(CONCATENATE(IF(FIND(A23;B$2:B$6),CONCAT($A$2:$A$6;", "),""))) 
 which works as long as I have only one assignment in column, because FIND function as in my example not work on arrayformula. 
Or am I missing something? another way I almost succeeded is : 
 =join(", ";(query(A$2:B$12;"select A where C contains 'cooking'")) 
this is working well for Monday. But what about Tuesday? Forget it! Now the problem - it eats only one array range, but I have names in first column only, and I need to ask for each column (monday, tuesday, etc) separately. And I don't know how to join columns so it appears for the query as one block. I mean, I need to feed the query with something like this: A2:A16;C2:C16 where column A are names, C is assignment_tuesday. Anything I was able to find are some crazy constructions like (CONCAT(SPLIT(TRANSPOSE(SPLIT(CONCAT(....))))) but everything I want to do is put it along for query - there must be easy way, isn't there? I wonder why the most simple things NEVER work, spreadsheet has TONS of functions used one time a year, but this? I imagine devs talking over the coffee: "Too simple guys, lets make it really annoying, should we?" 
following is a solution to the problem