3

I have a "database" table in sheets where each row is a person/email associated with a company and contact role. So, a company will have multiple rows of contacts for various roles (sales, marketing, etc) and can have more than one person that does the same role. My goal is to consolidate all people at a company that perform the same role into a column per role.

enter image description here

The goal is to go from each company having multiple rows to each company having a single row and the contacts consolidated into columns. "Partnership" row becomes a column, "Primary Sales" row becomes a column...etc.

The cells with red have calculations. This gets me close but I'm looking to perfect it!

=TEXTJOIN(", ",TRUE,QUERY($A$2:$D,"SELECT C,D WHERE A = '"&$F3&"' AND B = '"&G$2&"'"))
  1. Can a single calculation in cell G3 populate down for each company?
  2. Can the people be separated with a CR/LF rather than a comma e.g.


This
enter image description here


instead of
enter image description here

Example Data: https://docs.google.com/spreadsheets/d/17Nd6jYW_CkTb6Xju9JMDbRAGJnz-TXvcZOFU-lGQBdA/edit?usp=sharing
In the example, existing calculations are in red.

As always, thank you in advance for any help you can give me!

player0
  • 124,011
  • 12
  • 67
  • 124
Chris Rosendin
  • 305
  • 2
  • 14
  • Instead of `TEXTJOIN` I would use `CONCATENATE` and provide `CHAR(10)` wherever a new line is needed. –  May 15 '20 at 10:53

2 Answers2

2

paste in G3 and drag to the right:

=ARRAYFORMULA(IFNA(VLOOKUP($F3:$F, SUBSTITUTE(REGEXREPLACE(REGEXREPLACE(
 TRIM(SPLIT(TRANSPOSE(QUERY(QUERY({$B3:$B, $A3:$A&"♦", $C3:$C&", "&$D3:$D&"♠"}, 
 "select max(Col3) where Col1 = '"&G2&"' group by Col3 pivot Col2")
 ,,999^99)), "♦")), "♠ ", "♠"), "♠$", ), "♠", CHAR(10)), 2, 0)))

0

spreadsheet demo

Community
  • 1
  • 1
player0
  • 124,011
  • 12
  • 67
  • 124
0

Well, I have an answer to one of your questions at least. The carriage return character in google sheets can be accessed through CHAR(10); meaning you can replace your ", " with CHAR(10) and it'll make a new line between entries:

=TEXTJOIN(CHAR(10),TRUE,QUERY($A$2:$D,"SELECT C,D WHERE A = '"&$F3&"' AND B = '"&G$2&"'"))
Rafa Guillermo
  • 14,474
  • 3
  • 18
  • 54
BaconSodas
  • 74
  • 3
  • Unfortunately not quite; this will also make newlines between the name and the email address, not just between each person. – Rafa Guillermo May 15 '20 at 08:19