google-sheetssplitgoogle-sheets-formulacountif
Count and group equal values in the same cell in Google Sheets
In Google Sheets I want to create a formula that separates the values separated by "|" in the "Memory" column and count the same values, so that the result is the same as in the "Result" column:
| Memory | Result |
|---|---|
| 4GB | 4GB | 8GB | 8GB | 8GB | 8GB | 4GB | 3 x 4GB + 4 x 8GB |
| 4GB | 1 x 4GB |
| 4GB | 8GB | 1 x 4GB + 1 x 8GB |
Solution
You can use a formula like this, that splits the values and makes the count:
=BYROW(A2:A,LAMBDA(mem,IF(mem="",,
LET(splitted,SPLIT(mem," | "),
uniq, UNIQUE(TOCOL(splitted)),
counts, BYROW(uniq,LAMBDA(each,COUNTIF(splitted,each)&" x "&each)),
TEXTJOIN(" + ",1,counts)))))
