How can I get a QUERY to CONCATENATE text after it combines data?

user_14657305 0
Viewed 43

On this sheet, I've been working on I have the info I need adjusting down into the Query Results

=QUERY(Investors!A4:D, 
 "select B,C,sum(D) 
  where B is not null
  group by B,C
  label sum(D)''")

How do I get the Stock Numbers Due that day to be listed out in column D? Like this:

 1. 10/7/2019   Grady Johnson   $100.12     PT-1013
 2. 11/15/2020  Bill Jones      $553.80     PT-1020, PT-1019
 3. 11/15/2020  Grady Johnson   $45.00      PT-1011
 4. 11/15/2020  Steve Robinson  $320.00     PT-1018, PT-1016
 5. 11/17/2020  Jim Luke        $1,057.20   PT-1009, PT-1008, PT-1007, PT-1006
 6. 11/22/2020  Jim Luke        $300.43     PT-1010

Here's the link: Google Sheet

Any help is appreciated!

google-sheets-formula google-sheets concatenation
3 Answers

or paste this in row 3 and drag down:

=JOIN(", ", IFNA(FILTER(Investors!A$4:A, Investors!B$4:B&" "&Investors!C$4:C=A3&" "&B3)))

enter image description here

user_56326290

try:

=ARRAYFORMULA(REGEXREPLACE(IFNA(VLOOKUP(TO_TEXT(A3:A)&" "&B3:B, 
 TRIM({TRANSPOSE(QUERY(TRANSPOSE(QUERY(QUERY({Investors!A4:C}, 
 "select Col2,Col3,count(Col2) where Col2 is not null group by Col2,Col3 pivot Col1"), 
 "select Col1,Col2 offset 1", 0)),,9^9)), 
 TRANSPOSE(QUERY(TRANSPOSE(IF(QUERY(QUERY({Investors!A4:C}, 
 "select count(Col2) where Col2 is not null group by Col2,Col3 pivot Col1"), 
 "offset 1", 0)="",,
    QUERY(QUERY({Investors!A4:A&",", Investors!B4:C},
 "select count(Col2) where Col2 is not null group by Col2,Col3 pivot Col1"), 
 "limit 0", 1))),,9^9))}), 2, 0)), ",$", ))

enter image description here

user_56326290

Here is my suggested formula to be placed in Combined!D3 of your sample spreadsheet:

=ArrayFormula(IF(A3:A="",,SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF(VLOOKUP(TRANSPOSE(FILTER(Investors!A4:A,Investors!A4:A<>"")),{Investors!A:A,Investors!C:C&Investors!B:B},2,FALSE)=B3:B&A3:A,TRANSPOSE(FILTER(Investors!A4:A,Investors!A4:A<>"")),))," ",COUNTA(Investors!A4:A))))," ",", ")))

As for how it works, that would be time-consuming to explain. (More time than is reasonable for a free site such as this.) I will suggest that, if this is important to you, take the time to study it. Take it apart and see what each piece does — separately and then in combination with other parts — until you've gotten it all back together.

user_139032830
Related