Combine text from multiple cells into a single cell on other Sheet?

Question

I need help, I want to combine multiple cells in one other cell in another sheet.
I have 4 Collums in my first Sheet1, filed with data:

1. Id's (A1:A)
2. Dates(B1:B)
3. Name(C1:C)
4. Comments(D1:D)

And on my other Sheet2, I can select Id's(G3) from a dropdown and get more data from other sheets. Now I want to Combine all Dates, Name and Comment in one cell on my Sheet2.
I want something like, I pick an Id on Sheet2 and get every Comment with name and Date from Sheet1 in one cell.
I want it like

1.1.2018 peter Comment  
2.1.2018 hans Comment  
15.1.2018 peter Comment...

The cell can expand I only need everything in one cell.
Is that possible and how?

Answer 1

if you need it everything in one cell, try this:

=JOIN(CHAR(10); FILTER(ARRAYFORMULA(Sheet1!B1:B &" "&
                                    Sheet1!C1:C &" "&
                                    Sheet1!D1:D); Sheet1!A1:A=G3))

---

---

blank error fix:

=IF(G3<>""; JOIN(CHAR(10); FILTER(ARRAYFORMULA(Sheet1!B1:B &" "&
                                               Sheet1!C1:C &" "&
                                               Sheet1!D1:D); Sheet1!A1:A=G3));)

Answer 2

If you're using a drop down this should work

=TEXTJOIN(",", True,QUERY(A:D, "Select * Where A = '"&E2&"' ",0))

you will need to change your query to match ur data. share a copy of ur sheet if u can.

Answer 3

you need this in Sheet2!H3:

=FILTER(ARRAYFORMULA(Sheet1!B1:B&" "&Sheet1!C1:C&" "&Sheet1!D1:D); Sheet1!A1:A=G3)

---

---

#N/A fix:

=IFERROR(FILTER(ARRAYFORMULA(Sheet1!B1:B&" "&Sheet1!C1:C&" "&Sheet1!D1:D);Sheet1!A1:A=G3);)