CODEWITHSUNDEEP
sortinggoogle-sheetssplitarray-formulas
visualPaul
visualPaul

Reputation: 125

Google Sheets SORT comma separated text in cells using array formula

I have a column with tags like a, b, c, etc. Tags in the cell are unique, well separated with ", " (a comma and a space), but not sorted. I want to an array formula to return a column with sorted tags in each cell. for example In A1:A5

c, a

b, a, d

a, c

c

c, b, a

I want an array formula in B1 to return results in B1:B5

a, c

a, b, d

a, c

c

a, b, c

After sorting in cells I can tell that tags "c, a" is the same as tags "a, c". My formula only return only one cell

=ARRAYFORMULA(JOIN(", ",SORT(TRANSPOSE(SPLIT(A1:A5,", ",FALSE)))))

Upvotes: 0

Views: 3855

Answers (2)

Max Makhrov
Max Makhrov

Reputation: 18717

ArrayFormula:

=ArrayFormula(TRIM(TRANSPOSE(SPLIT(CONCATENATE(REPT(TRANSPOSE(QUERY(SORT({TRANSPOSE(SPLIT(JOIN(", ",ArrayFormula(REGEXREPLACE(A1:A5,"\w+",TEXT(row(A1:A5),"0")))),", ")),TRANSPOSE(SPLIT(JOIN(", ",A1:A5),", "))},1,1,2,1),"select Col2",0)&", "),ROW(A1:A5)=SPLIT(JOIN(", ",ArrayFormula(REGEXREPLACE(A1:A5,"\w+",TEXT(row(A1:A5),"0")))),", "))&REPT(" "&CHAR(9),TRANSPOSE(row(OFFSET(A1,,,COUNTA(SPLIT(JOIN(", ",A1:A5),", "))))-row($A$1)+1)=COUNTA(SPLIT(JOIN(", ",A1:A5),", ")))),",  "&CHAR(9),0))))

I used this tip to resolve the task.

Update

With some edits, this formula can also handle comma separated phrases:

=ArrayFormula(TRIM(TRANSPOSE(SPLIT(CONCATENATE(REPT(TRANSPOSE(QUERY(SORT({TRANSPOSE(SPLIT(JOIN(",",ArrayFormula(REGEXREPLACE(A1:A5,"[^,]+",TEXT(row(A1:A5),"0")))),",",0)),TRANSPOSE(SPLIT(JOIN(", ",A1:A5),", ",0))},1,1,2,1),"select Col2",0)&", "),ROW(A1:A5)=SPLIT(JOIN(",",ArrayFormula(REGEXREPLACE(A1:A5,"[^,]+",TEXT(row(A1:A5),"0")))),",",0))&REPT(" "&CHAR(9),TRANSPOSE(row(OFFSET(A1,,,COUNTA(SPLIT(JOIN(", ",A1:A5),", ",0))))-row($A$1)+1)=COUNTA(SPLIT(JOIN(", ",A1:A5),", ",0)))),",  "&CHAR(9),0))))

file sample. The key is REGEXREPLACE part of formula: "[^,]+"

Upvotes: 3

Ed Nelson
Ed Nelson

Reputation: 10259

Arrayformula and split will not work on more than one row. The best I can think of is to fill the formula down with script. You could do it onOpen or onEdit.

function fillformuladown() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var lastRow = ss.getDataRange().getNumRows();  
  var rngVal = ss.getRange("k1").getFormula()
  ss.getRange("b1:b"+lastRow).setFormula(rngVal)
}

Upvotes: 1

Related Questions