Yogi Anand, D.Eng, P.E. Google Spreadsheet Post #471 www.energyefficientbuild.com
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
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
No comments:
Post a Comment