CODEWITHSUNDEEP
google-sheetsformulasarray-formulas
Vin
Vin

Reputation: 2165

ArrayFormula a Filter in a Join (Google Spreadsheets)

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!

Upvotes: 5

Views: 10552

Answers (3)

iki
iki

Reputation: 121

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])```

Upvotes: 0

Sérgio Sá
Sérgio Sá

Reputation: 45

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

Upvotes: 4

AdamL
AdamL

Reputation: 24659

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)

Upvotes: 4

Related Questions