Yogi Anand, D.Eng, P.E. Google Spreadsheet www.energyefficientbuild.com
user davesmithdub said:
How do I do a kind of pivot and concatenate
Hi
I've prepared a spreadsheet to show what the situation is: https://docs.google.com/spreadsheet/ccc?key=0AiRLtUqKvuCbdFYtU2I4M3ZEelBXM0RxQTJGS0VKOEE
.. and here's an explanation: The above link has a Source Array with two columns: Names and Dates.
What I want is to have an output where there are two columns: Dates, which contains unique dates and Names, which contains concatenated names (because there can be more than one name per date)
I looked into doing this with something like =Query(A:B, "select B pivot A") but I'm hitting a wall with a missing aggregation function.
all the best
Dave
------------------------------------------
following is a solution to the problem
user davesmithdub said:
How do I do a kind of pivot and concatenate
Hi
I've prepared a spreadsheet to show what the situation is: https://docs.google.com/spreadsheet/ccc?key=0AiRLtUqKvuCbdFYtU2I4M3ZEelBXM0RxQTJGS0VKOEE
.. and here's an explanation: The above link has a Source Array with two columns: Names and Dates.
What I want is to have an output where there are two columns: Dates, which contains unique dates and Names, which contains concatenated names (because there can be more than one name per date)
I looked into doing this with something like =Query(A:B, "select B pivot A") but I'm hitting a wall with a missing aggregation function.
all the best
Dave
------------------------------------------
following is a solution to the problem
Hi Yogi, I think this post is very interesting, so I took the liberty to made the next observation:
ReplyDeleteIn the cell E5, the formula write an "," character. Also the redaction of the formula includes the part {3,4,5,6,7,8,0} that implies certain manual work. In order to improve the formula I suggest the next modification:
=arrayformula(iferror(if({1,0},unique(B2:B),transpose(split(concatenate(if(transpose(if(row(A:A)=count(row(A:A)),0,1)),if(unique(B2:B)=transpose(B2:B),transpose(A2:A),""),char(10))&char(9)),char(10))))))
A little bit more large and also a little bit more automatic.
The thing I can't do is to make the formula to give the next answer:
Bob, Larry, Jenny
Harry, Steph
Sarah
I think the solution is about concatenating the right combination of characters.
Cheers!
Hi Carlos HG:
ReplyDeleteThanks for your feedback ... very interesting -- let me look at it in more detail before I report back.
Cheers!
Yogi