ArrayFormula a Filter in a Join (Google Spreadsheets)

google-sheets formulas array-formulas
Vin picture Vin · Jan 21, 2013 · Viewed 8k times · Source

I've done a lengthy search and couldn't find what I'm looking for. Maybe someone out there can 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 is first names (e.g. 'James') and Column B is some comment (e.g. 'Headache'). James (or anyone else) may have multiple rows with different comments in each one (e.g James has 2 rows, one saying 'Headache' and another saying 'Knee pain'.)

In sheet 2, column A, I have a list of the names that appear in Sheet1 (Using the '=UNIQUE' formula). A1 says 'James'. In cell B1 I input the above formula.

The result is almost exactly what I want. It joins all of James' comments into one cell, with a space " " between each comment. So the result in cell B1 is: 'Headache Knee pain'.

However, I have to drag this formula to all the cells below. Does anyone know how I can make this like all the other ArrayFormulas I've used in the past, where the formula automatically fills all the cells below? I have tried making it an array formula but with no success.

I have also been playing around with this formula which gives me the same result 'Headache Knee pain', but the formula still won't copy into the cells below.

=SUBSTITUTE(Arrayformula(concatenate(FILTER(Sheet1!B:B;Sheet1!A:A=A1)&" "; "|"));" |";"")

If anyone knows how to achieve this I will be very grateful indeed - your valuable help will be much appreciated.

Thanks for looking!

Answer

AdamL picture AdamL · Jan 21, 2013

It is generally a bit complicated to apply an aggregating function like CONCATENATE row-by-row.

=ArrayFormula(TRIM(TRANSPOSE(SPLIT(CONCATENATE(REPT(TRANSPOSE(Sheet1!B:B&" ");A:A=TRANSPOSE(Sheet1!A:A))&REPT(" "&CHAR(9);TRANSPOSE(ROW(Sheet1!A:A))=ROWS(Sheet1!A:A)));CHAR(9)))))

(edit: apologies I haven't had the opportunity to test for bugs/typos, will remove this line if you can confirm it works)

Related questions

Linking one google docs spreadsheet to another

I have data in one google docs. Call it docA. I would like to reference this data in docB. Something along the lines of =docA!Sheet1!A2 How would I be able to go about this? I do not mind …

Read More
Google SpreadSheet Query: Can I remove column header?

I'm doing this query at my google spreadsheet: =QUERY(H4:L35;"select sum(L) where H='First Week'"; -1) But it returns a little table with "sum" as header and result below it. What I want is just the result! …

Read More
How to format a duration as HH:mm in the new Google sheets

In the new Google sheets there's a way of formatting a number as a duration. Format -> Number -> Duration. 1 is formatted as 24:00:00 1.2 is formatted as 28:48:00 1.5 is formatted as 36:00:00 0.03125 is formatted as 0:45:00. I don't need the seconds in …

Read More