Search code examples
excellibreofficelibreoffice-calc

Flatten column values to comma seperated list

Given the following columns in a Excel compatible spreadsheet:

| CAT1 | CAT2 | CAT3 | 
|      |      | 1    | 
|      |    2 | 1    | 
| 1    |    3 | 2    |

I want to achieve:

| CAT1 | CAT2 | CAT3 | FLAT
|      |      | 1    | 1
|      |    2 | 1    | 2,1
| 1    |    3 | 2    | 1,3,2

with an Excel Function (or a combination of functions).

Is there something build in for that?

Solution

  • Assuming CAT1 in A1:

    =TEXTJOIN(",",1,A2:C2)

    in D2 and copied down to suit. Tested in LibreOffice Calc.