[Solved] Remove duplicates in one text cell

Creating a macro - Writing a Script - Using the API

[Solved] Remove duplicates in one text cell

Postby eugenechabanov » Fri Feb 14, 2014 11:24 am

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   Expand viewCollapse view
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
eugenechabanov
 
Posts: 8
Joined: Fri Feb 14, 2014 11:15 am

Re: Remove duplicates in one text cell

Postby Villeroy » Fri Feb 14, 2014 1:31 pm

Works for me.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24594
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Remove duplicates in one text cell

Postby eugenechabanov » Fri Feb 14, 2014 3:49 pm

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
eugenechabanov
 
Posts: 8
Joined: Fri Feb 14, 2014 11:15 am

Re: Remove duplicates in one text cell

Postby Villeroy » Fri Feb 14, 2014 4:04 pm

Try this version:
Code: Select all   Expand viewCollapse view
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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24594
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Remove duplicates in one text cell

Postby eugenechabanov » Fri Feb 14, 2014 6:08 pm

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
eugenechabanov
 
Posts: 8
Joined: Fri Feb 14, 2014 11:15 am

Re: Remove duplicates in one text cell

Postby Villeroy » Fri Feb 14, 2014 6:12 pm

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24594
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Remove duplicates in one text cell

Postby hiklot » Fri May 05, 2017 11:03 am

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   Expand viewCollapse view
Los Angeles, California, CA


will only return
Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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
hiklot
 
Posts: 1
Joined: Fri May 05, 2017 10:50 am


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 10 guests