Return list of delimited text values based on criteria

[chrisboat]

Trying to figure out how to do this. Have tried the UDF shown below and cannot get it to run so am looking for either another simpler method or a bit of help solving the UDF.
Lookup Values Column C
Lookup Range Column A
Return Range Column B
Resulting Values I want Column D

Code: Select all

A          B          C        D
Person  Colour	Person   Colours
Bob	  Red      Adam	  Red
Adam    Red      Bob	   Red,Green,Blue
Bob     Green	 Peter	 Blue,Yellow
Bob     Blue			
Peter   Blue			
Peter   Yellow

Found a UDF written for Excel that looked like it might work but I get an error on the line
"temp = IIf(cell = val, temp & rtn(i) & delim, temp)" Inadmissible value or data type.
Index out of defined range.

Code: Select all

REM  *****  BASIC
Function TEXTIF(val As Variant, rng As Range, rtn As Range, Optional delim As String) As String
  'https://www.reddit.com/u/excelevator
  'https://old.reddit.com/r/excelevator
  'https://www.reddit.com/r/excel - for all your Spreadsheet questions!
'TEXTIF ( LOOKUP_VALUE , LOOKUP_RANGE , RETURN_RANGE , [OPTIONAL] DELIMITER )
If IsEmpty(delim) Then
    delim = ""
End If
Dim temp As String
Dim i As Integer
i = 1
    For Each cell In rng
     temp = IIf(cell = val, temp & rtn(i) & delim, temp)
     i = i + 1
   Next
TEXTIF = Left(temp, Len(temp) - Len(delim))
End Function

[mikele]

Hello,
a simple solution would be the use of data pilots (or pivot tables). One of my examples won't work under AOO, due to the function TEXTJOIN(), but work under LO.

[robleyd]

Same question posted on AskLibreOffice

[Lupp]

As "mikele" already mentioned there is a standard function TEXTJOIN() capable of doing the trick. It was introduced by Excel 2016 (if I remeber correctly) and implemented also in LibreOffice V5.2.2 then with serious flaws. Since LibO V5.2.4 it is working well.

However, there are also reasons to stick to AOO - or to stay compatible with Excel where only very few recent versions have the function. Long before TEXTJOIN() was implemented I had written a couple of functions in that range, not only joining texts, but also splitting them, and offering some additional features. They are included with the attached example. Unfortunately the additional features made them a bit more complicated, and you will need to simplify them if you want to get concise code.