CODEWITHSUNDEEP
google-sheets
student_alex
student_alex

Reputation: 43

ARRAYFORMULA with FILTER and CONCATENATE inside

I have table like this. In real life it will be have more than 1000 rows.

Client | Group
--------------
Merry  | A
Merry  | C
Susan  | B
Mike   | C
Susan  | A
Joe    | A
Tom    | B
Tom    | C

And I have list of all clients which is filled from Google Form. I want to fill Group column with groups of every client using only arrayformula in the first row. My desired output is this:

Name    | Groups
----------------
Joe     | A
Tom     | B, C
Mike    | C
Merry   | A, C
Susan   | A, B
Peter   |

My best result yet is this:

Name    | Groups |   |   |   |   |   |   |   |
----------------------------------------------
Joe     |        |   |   |   |   | A |   |   |
Tom     |        |   |   |   |   |   | B | C |
Mike    |        |   |   | C |   |   |   |   |
Merry   |   A    | C |   |   |   |   |   |   |
Susan   |        |   | B |   | A |   |   |   |
Peter   |        |   |   |   |   |   |   |   |

Looks like it does what I want but I can't join all these columns into one. Formula is:

=ArrayFormula(IF(name<>"";IF(name=TRANSPOSE(FILTER(client;LEN(client)));TRANSPOSE(FILTER(group;LEN(group))););))

I used "name", "client" and "group" as named ranges according to column headers.

Sample sheet is here (view only): https://docs.google.com/spreadsheets/d/1VFnmyDTQhlCaANAvkzLgzKRKchPsGzFn4hDZYOoJF_M/edit?usp=sharing

Upvotes: 4

Views: 4466

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(GroupColumn, ClientColumn, NameColumn, ", ").

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

JPV
JPV

Reputation: 27302

In addition to Rubén's solution, also try

=arrayformula( regexreplace( { unique(A3:A), trim( transpose( query( if( ( transpose(unique(A3:A)) = A3:A ) * len(A3:A), B3:B & ",", ), "select *", 50000) ) ) }, ",$", "" ) )

Note: depending on you locale, you may have to use this formula instead:

=arrayformula( regexreplace( { unique(A3:A)\ trim( transpose( query( if( ( transpose(unique(A3:A)) = A3:A ) * len(A3:A); B3:B & ","; ); "select *"; 50000) ) ) }; ",$"; "" ) )

Upvotes: 3

Wicket
Wicket

Reputation: 38435

Considering that the "best result" is on H3:O8 on the linked spreadsheet at this time.

The following formula returns the desired results:

=ArrayFormula(substitute(transpose(trim(query(transpose(H3:O8);"select *";ROWS(A:A))));" ";","))

Explanation

QUERY concatenates the rows values separating them by a space character. The above formula use this feature to concatenate the result got so far by the OP, then TRIM is used to remove the extra spaces and SUBSTITUTE to replace the remaining spaces by a comma.

Upvotes: 2

Related Questions