I did a long search and could not find what I was looking for. Maybe someone out there might kindly help?
I have this formula in my google spreadsheet (I will explain what it does below):
=Join(" ",FILTER(Sheet1!B:B;Sheet1!A:A=A1))
In sheet 1 is a table: Column A first names (for example, "James") and in column B some comment (for example, "Headache"). James (or any other) can have several lines with different comments in each of them (for example, James has 2 lines, one saying “Headache” and the other saying “Knee pain”.)
In sheet 2, in column A, I have a list of names that appear in Sheet1 (using the formula '= UNIQUE'). A1 says James. In cell B1, enter the above formula.
The result is almost what I want. It combines all of James's comments in one cell with a space “between each comment. Thus, the result in cell B1:“ Knee headache. ”
However, I need to drag this formula into all cells below. Does anyone know how I can do this, like all the other ArrayFormulas I have used in the past, where the formula automatically fills all the cells below? I tried to make it an array formula, but without success.
I also played with this formula, which gives me the same “Knee Headache” result, but the formula will still not copy to the cells below.
=SUBSTITUTE(Arrayformula(concatenate(FILTER(Sheet1!B:B;Sheet1!A:A=A1)&" "; "|"));" |";"")
- , , - .
, !