Google sheet array formula with Filter & Join

Question

I need to build a table based on the following data:

Ref	Product
R1	ProdA
R2	ProdC
R1	ProdB
R3	ProdA
R4	ProdC

And here the result I need:

My Product	All Ref
ProdA	R1#R3
ProdC	R2#R4

The particularity is that the 'My Product' column is computed elsewhere. So I need an arrayformula based on 'My Product' column to look in the first table to build the 'All Ref' column. You follow me?

I know that Arrayformula is not compatible with filter and join ... I expect a solution like this one Google sheet array formula + Join + Filter but not sure to understand all steps and if really adapted to my case study.

Hope you can help.

Answer 1

You could try something like this:

CREDIT: player0 for the method shared to similar questions

=ARRAYFORMULA(substitute(REGEXREPLACE(TRIM(SPLIT(TRANSPOSE(
 QUERY(QUERY({B2:B&"?", A2:A&"#"}, 
 "select max(Col2) 
  where Col1 !='' 
  group by Col2 
  pivot Col1"),,999^99)), "?")), "#$", )," ",""))

Step by step:

Answer 2

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(MyProductColumn, ProductColumn, RefColumn, "#").

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