Concatenating values from multiple matches into single cell

Question

I have a spreadsheet which I am trying to set up as follows:

A    | B       | C       | D         | ... | G        | H
Date   Trainer   Trainee   Procedure         Employee   Credentials
...    Aster     Lisa      A                 Aster      A
...    Frank     Lisa      B                 Frank      C, A
...    Frank     Aster     A                 Lisa       A, B, C
...    Lark      Frank     C                 Lark       
...    Lark      Frank     A
...    Frank     Lisa      C

My question is what sort of formula could I use to have the credentials column contain a concatenated list of every procedure whose trainee entry matches the employee entry? Ideally it shouldn't be necessary to sort the data, as it is already sorted by date.

Answer 1

I suggest a solution that will work in LibreOffice Calc 5.4. It uses a built in TEXTJOIN Function that's only available in recent versions of LibreOffice Calc. So in case you are using an older version, try to upgrade to v5.4.

In this example sample data is in Cells C2:D8. Also leave first row as header row for the formula to work correctly, as the first row is refereed in the solution to work correctly.

Trainee names are put in cells E2:E6.

Now in F2 put the following formula.

=IFERROR(TEXTJOIN(",",1,IFERROR(INDEX($D$2:$D$8,N(IF(1,IF($C$2:$C$8=E2,ROW($D$2:$D$8)-ROW($D$1),9^99)))),"")),"")

Press CTRL + SHIFT + ENTER (CSE) from within the Formula Bar to create an Array Formula. Without CSE the formula will not work correctly.

Now copy and paste this formula to all applicable cells in F3 thru F6. This is tested on Windows OS, should work fine though even if you are on Linux.

Since your Tag mentions LibreOffic Calc, this solution is bit specific to LibreOffice Calc. If you are on MS Excel TEXTJOIN is only available in Office 2016 and I guess Office 365 version. In that case if TEXTJOIN is not natively available you need to code one in VBA.

Answer 2

The various ways to do this in a spreadsheet are described at Excel - How to vlookup to return multiple values? Primarily, this is done with array formulas that would find one "Procedure" in each cell. Then these cells could be joined to show "Credentials".

However, my choice would be to use Base with an engine such as MySQL. The query would use GROUP_CONCAT() as described at https://stackoverflow.com/a/194875/5100564. The data can be moved to Base by dragging and dropping a selection from Calc to the Tables icon.

Newer versions of HSQL also support GROUP_CONCAT. However, the old HSQL 1.8 that comes built-in with LibreOffice does not.