Google-sheets – Google Sheets combine all selected rows into single cell separated by commas

google sheets

I'm trying to combine all selected rows into a single cell.

A
B
C
D
E
...Z

or alternatively, all selected.

Into a single cell as A, B, C, D, E where I can copy its values.

I have tried to concat, and merge unsuccessfully, as merging deletes all values but A and concat only combines the instructed values.

Best Answer

I believe I found the answer while looking into VB Scripts, for Google Sheets it's Google Apps Scripts

https://developers.google.com/apps-script/reference/spreadsheet/range#mergevertically

Formula

=JOIN(
   CHAR(10),
   ARRAYFORMULA(
     QUERY(
       Transactions!A2:D, 
       "SELECT B WHERE A = date '" & text(Summary!A2,"yyyy-MM-dd") & "'"
     ) 
     & ": " &   <=== concatenate
     QUERY(
       Transactions!A2:D, 
       "SELECT D WHERE A = date '" & text(Summary!A2,"yyyy-MM-dd") & "'"
     )
   )
 )

 copy/paste         
 =JOIN(CHAR(10),ARRAYFORMULA(QUERY(Transactions!A2:D, "SELECT B WHERE A = date '" & text(Summary!A2,"yyyy-MM-dd") & "'") & ": " & QUERY(Transactions!A2:D, "SELECT D WHERE A = date '" & text(Summary!A2,"yyyy-MM-dd") & "'")))

Explained

The two QUERY functions, both deliver each part of the data you want to present: the description and the value. These two are then "glued" together, via the ampersands and per row, via the ARRAYFORMULA. Each row is then joined, via the JOIN function, using a line feed (CHAR(10)) as delimiter.

Screenshot

enter image description here

Note

The huge benefit over a script is the immediate re-calculation of the data, when changes occur. Just copy the formula downwards to get all the results. You can add your own logic to remove empty fields.

Example

I've prepare an example file for you: Notes

Google-sheets – For range X, when empty grab column A and list them in a single cell, coma separated

Use JOIN subject to FILTER by ISBLANK. For the example spreadsheet below, the command

=JOIN(", ", FILTER(A1:A6, ISBLANK(B1:B6)))

returns "Robert, Jay, Tim"

      A          B
1    Jon      attacked   
2    Robert     
3    Grace    attacked  
4    Jay        
5    Tim        
6    Shog     attacked