CODEWITHSUNDEEP
google-sheetstransposearray-formulasstring-concatenationtextjoin
Chris Rosendin
Chris Rosendin

Reputation: 367

Google Sheets Text Join to consolidate rows into columns

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!

Upvotes: 4

Views: 2523

Answers (2)

player0
player0

Reputation: 1

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

Upvotes: 2

BaconSodas
BaconSodas

Reputation: 74

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&"'"))

Upvotes: 0

Related Questions