[Solved] Remove duplicates in one text cell

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
eugenechabanov
Posts: 8
Joined: Fri Feb 14, 2014 11:15 am

[Solved] Remove duplicates in one text cell

Post by eugenechabanov »

Hey there! Please help me out, I have one single cell containing comma separated text , e.g. "test1, test2, test3, test1, test4, test2, test5".
And I need macros that will return the same string without duplicates, i.e. "test1, test2, test3, test4, test5".

I've found VBA for Excel but I can't translate it to Open Office working script.
http://www.ozgrid.com/forum/showthread.php?t=69682

This one works in Excel but not in Calc:

Code: Select all

Function stringOfUniques(inputString As String) As String 
    Dim inArray() As String 
    Dim xVal As Variant 
    inArray = Split(inputString, ",") 
    For Each xVal In inArray 
        If InStr(stringOfUniques, Trim(xVal)) = 0 Then _ 
        stringOfUniques = stringOfUniques & Trim(xVal) & "," 
    Next xVal 
End Function 
Any help would be must appeciated!
Last edited by eugenechabanov on Fri Feb 14, 2014 6:10 pm, edited 1 time in total.
Apache Open Office 3.4.1 Win7 64
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Remove duplicates in one text cell

Post by Villeroy »

Works for me.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
eugenechabanov
Posts: 8
Joined: Fri Feb 14, 2014 11:15 am

Re: Remove duplicates in one text cell

Post by eugenechabanov »

How do you use it?

If A1 contains "test1, test2, test3, test1, test4, test2, test5"
Putting "=STRINGOFUNIQUES(A1)" into any other string returns comma "," in my case.
Concerning the script itself, I opened My macros and dialogs.Standard and pasted it after the end of Main (empty sub).
Apache Open Office 3.4.1 Win7 64
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Remove duplicates in one text cell

Post by Villeroy »

Try this version:

Code: Select all

Function stringOfUniques(inputString As String) As String
    Dim inArray() As String
    Dim xVal As Variant
    Dim s As String
    inArray = Split(inputString, ",")
    For Each xVal In inArray
        If InStr(s, Trim(xVal)) = 0 Then _
        s = s & Trim(xVal) & ","
    Next xVal
    stringOfUniques = s
End Function
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
eugenechabanov
Posts: 8
Joined: Fri Feb 14, 2014 11:15 am

Re: Remove duplicates in one text cell

Post by eugenechabanov »

Thanks Villeroy, it worked. Even the first code works, it was the matter of saving all files plus cell refreshing. Experience gained, topic closed. ; )
Apache Open Office 3.4.1 Win7 64
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Remove duplicates in one text cell

Post by Villeroy »

eugenechabanov wrote:Thanks Villeroy, it worked. Even the first code works, it was the matter of saving all files plus cell refreshing. Experience gained, topic closed. ; )
The original version behaves very strange. When you change the referenced string, the old return value is concatenated with the new value. The function variable seems to be static. My fixed version works normally (and it should work with Excel too).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
hiklot
Posts: 1
Joined: Fri May 05, 2017 10:50 am

Re: [Solved] Remove duplicates in one text cell

Post by hiklot »

This function was exactly what I was searching for today.

However I found a problem with the function, if you have a word that is already contained in a previous word.

For example

Code: Select all

Los Angeles, California, CA
will only return

Code: Select all

Los Angeles,California
because CA is already contained in California

I created a a quick and dirty solution for this problem by adding a comma to the InStr search:

If InStr(s, Trim(xVal) & ",") = 0 Then _

Code: Select all

Function stringOfUniques(inputString As String) As String
    Dim inArray() As String
    Dim xVal As Variant
    Dim s As String
    inArray = Split(inputString, ",")
    For Each xVal In inArray
        If InStr(s, Trim(xVal) & ",") = 0 Then _
        s = s & Trim(xVal) & ","
    Next xVal
    stringOfUniques = s
End Function
just in case anyone else needs this.
OpenOffice 4.1.3 on Windows 10
Post Reply