Return list of delimited text values based on criteria
Posted: Mon Feb 11, 2019 10:07 pm
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
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.
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"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