5

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!

Rubén
  • 34,714
  • 9
  • 70
  • 166
Vin
  • 1,975
  • 5
  • 27
  • 34

3 Answers3

4

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)

AdamL
  • 23,691
  • 6
  • 68
  • 59
  • Hi there, Your formula works perfectly, thank you very much indeed. While I was experimenting I realised I could just use this formula: =Join(" ",FILTER(Sheet1!B:B;Sheet1!A:A=A1)) but I still couldn't make it into an array formula. If it's simple to make this an array formula, please let me know. Otherwise the formula you have already given is fine, I'm completely happy with it anyway - I'm just curious if using the Join formula would have been simpler! Many thanks again. – Vin Jan 21 '13 at 22:46
  • Hello Again, Unfortunately, your formula doesn't work as perfectly as I'd first thought. It seems to work fine but when I delete a row from Sheet 1, the formula fails. It returns "--" down the whole column. Any ideas what is causing this problem? Thanks – Vin Jan 22 '13 at 04:06
4

I know the question is too old, but to solve the problem when erasing a line you need to change A:A to filter(A:A,A:A<>"") so, 

=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)))))

becomes:

=ArrayFormula(TRIM(TRANSPOSE(SPLIT(CONCATENATE(REPT(TRANSPOSE(Sheet1!B:B&" ");filter(A:A,A:A<>"")=TRANSPOSE(Sheet1!A:A))&REPT(" "&CHAR(9);TRANSPOSE(ROW(Sheet1!A:A))=ROWS(Sheet1!A:A)));CHAR(9)))))
marikamitsos
  • 10,264
  • 20
  • 26
Sérgio Sá
  • 45
  • 4
0

Instead of the workaround hacks I implemented a simple joinMatching(matches, values, texts, [sep]) function in Google Apps Script.

In your case it would be just =joinMatching(A1:A, Sheet1!A1:A, Sheet1!B1:B, " ").

Source:

// Google Apps Script to join texts in a range where values in second range equal to the provided match value
// Solves the need for `arrayformula(join(',', filter()))`, which does not work in Google Sheets
// Instead you can pass a range of match values and get a range of joined texts back

const identity = data => data

const onRange = (data, fn, args, combine = identity) =>
  Array.isArray(data)
    ? combine(data.map(value => onRange(value, fn, args)))
    : fn(data, ...(args || []))

const _joinMatching = (match, values, texts, sep = '\n') => {
  const columns = texts[0]?.length
  if (!columns) return ''
  const row = i => Math.floor(i / columns)
  const col = i => i % columns
  const value = i => values[row(i)][col(i)]
  return (
    // JSON.stringify(match) +
    texts
      .flat()
      // .map((t, i) => `[${row(i)}:${col(i)}] ${t} (${JSON.stringify(value(i))})`)
      .filter((_, i) => value(i) === match)
      .join(sep)
  )
}

const joinMatching = (matches, values, texts, sep) =>
  onRange(matches, _joinMatching, [values, texts, sep])```
iki
  • 101
  • 1
  • 7