Is there a way to conditionally JOIN records in google sheets?

Caleb Horton 0
Viewed 27

I have a spreadsheet containing a list of municipal offices and which county they are in. I want to join each record associated with a given county into one cell. Kind of like a "JOINIF" function if such a thing existed. I'm stumped on how to do this!

Spreadsheet looks like this:

Alameda | Rowan City Council
Alameda | Rowan Mayor
Alameda | Chester School Board
Jones | Fillips School Board
Jones | Smith School Board
Carol | Apple City Council
Carol | Pear City Council

At first I thought I could do it by putting this in the cell to the right of a given local office: =JOIN(", ",IF(A2=A:A,D2:D,"")), but this obviously just joins ALL records if county = county, which it always will. So I tried reversing the operation: =IF(A:A=A2,JOIN(", ",B2:B),"") but this does much the same. What am I missing here!

edit the desired output would look like this:

Alameda | Rowan Mayor | Rowan City Council, Rowan Mayor, Chester School Board
Alameda | Chester School Board | Rowan City Council, Rowan Mayor, Chester School Board
Jones | Fillips School Board | Fillips School Board, Smith School Board
Jones | Smith School Board | Fillips School Board, Smith School Board
Carol | Apple City Council | Apple City council, Pear City Council
Carol | Pear City Council | Apple City council, Pear City Council

The end goal being that I could then select columns A and C and remove duplicates, leaving me with a list with one row for each county, with column A being the county name and column B being the joined list of local offices in that county.

google-sheets-formula join google-sheets
1 Answers

try:

=ARRAYFORMULA(REGEXREPLACE(TRIM(SPLIT(FLATTEN(
 QUERY(QUERY({ROW(A1:A), A1:A&"×", B1:B&","}, 
 "select max(Col3) where not Col2 starts with '×' 
  group by Col1 pivot Col2"),,9^9)), "×")), ",$", ))

enter image description here

player00
Related