Return list of delimited text values based on criteria

Discuss the spreadsheet application

Return list of delimited text values based on criteria

Postby chrisboat » 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
Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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
OpenOffice 4.1.6 on Windows 10
chrisboat
 
Posts: 1
Joined: Mon Feb 11, 2019 9:13 pm

Re: Return list of delimited text values based on criteria

Postby mikele » Mon Feb 11, 2019 11:09 pm

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.
Attachments
delimited text list.ods
(14.2 KiB) Downloaded 10 times
LibreOffice 5.4.7 on LinuxMint/WinXP/Win7
mikele
 
Posts: 35
Joined: Wed Nov 21, 2018 11:11 am
Location: Germany

Re: Return list of delimited text values based on criteria

Postby robleyd » Tue Feb 12, 2019 12:15 am

Same question posted on AskLibreOffice
Cheers
David
Apache OpenOffice 4.2.0 Build 9820 alpha version - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2512
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Return list of delimited text values based on criteria

Postby Lupp » Tue Feb 12, 2019 12:29 am

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.
Attachments
aoo96962MultiLookupWithTextjoin_1.ods
(15.31 KiB) Downloaded 7 times
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2339
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to Calc

Who is online

Users browsing this forum: Moon Lit Nights and 37 guests