Wednesday, February 22, 2012

yogi_Concatenate Grouped Attributes For A Specified Column

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

2 comments:

  1. Hi Yogi, I think this post is very interesting, so I took the liberty to made the next observation:

    In 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!

    ReplyDelete
  2. Hi Carlos HG:

    Thanks for your feedback ... very interesting -- let me look at it in more detail before I report back.

    Cheers!
    Yogi

    ReplyDelete