Removing/Deleting repeated items from an array

[LO_user4]

Dear Team

Hello and Hopping for your Good Health and Safety.

Please note that I want to remove some repeating items from an array.

My array shown below:

Ary(0) = “empty”
Ary(1) = “cat”
Ary(2) = “empty”
Ary(3) = “empty”
Ary(4) = “dog”
Ary(5) = “rate”
Ary(6) = “hen”

and after the execution of the macro I should get following:

Ary_cln(0) = “cat”
Ary_cln(1) = “dog”
Ary_cln(2) = “rate”
Ary_cln(3) = “hen”

From the internet I got a solution which is not working. Please help me out the code is shown below:

Sub test

Dim Ary(0 to 6) As String

Ary(0) = "empty"
Ary(1) = "cat"
Ary(2) = "empty"
Ary(3) = "empty"
Ary(4) = "dog"
Ary(5) = "rate"
Ary(6) = "hen"

Dim Ary_cln() As String
ReDim Ary_cln(0 To 6)
For i = LBound(Ary) To UBound(Ary)
If Trim(Ary(i)) <> "empty" Then    ''' trim is use to remove space
Ary_cln(i) = Ary(i)   '''input non black values in the clean array
End If
Next i

MsgBox UBound(Ary_cln)
For i = LBound(Ary_cln) to UBound(Ary_cln)
msg = msg & "index " & i & "--" & Ary_cln(i) & Chr(10)
Next i
MsgBox msg, 0, "Array Items"

End Sub

but when I run the code the Ary_cln is equal to Ary with “empty” replaced with “”. refer following snapshot

Please help me in this regards.

Yours Sincerely
Muhammad Ali

[Lupp]

What version of LibO are you using?

• Do you actually want a single-sequence-array as input?
  You would this only get by elementwise definition, or as the output of another piece of code, say by a call to SPLIT().
• Do you need the output to work with it in user code, or do you want to return the result to a CellRange in a Calc sheet?
• Do you know that recent versions of LibO Calc come with a function named UNIQUe() which is made for jobs of the kind?

For your exeriments (if any):
disask_136039_select_distinct_experiments.ods (17.7 KB)

[karolus]

hallo

_input = ["empty", "cat", "empty", "empty", "dog", "rate", "hen"]
_output = [ entry for entry in _input if _input.count(entry) == 1]
print( "\n".join(_output))

cat
dog
rate
hen

thats python, … probably you should use apso.oxt from here to manage your python stuff

[LO_user4]

Dear Brother Karolus

Much Thanks for your input and time in solving the issue in python language.

However, please note that I am new to python and want to use the basic macro language for my working. So, please do the needful and provide a reply in basic macro language.

I appreciate you for your time and efforts in this regards.

Yours Sincerely
Muhammad Ali

[karolus]

Sub Main
    func_access = createUnoService( "com.sun.star.sheet.FunctionAccess" )
    _input = array("empty", "cat", "empty", "empty", "dog", "rate", "hen")
    _out = func_access.callFunction("unique", array( array(_input), 1, 1))
    for each entry in _out(0)
        msgbox entry
    next
End Sub

[Wanderer]

@LO_user4

and want to use the basic macro language

While I like @karolus suggestions (especially using the functions already available in Calc) your “solution” by debugging your script was already done by @fpy : You need two counters to index the source and destination arrays.
.
Is your intent to learn BASIC?

[LO_user4]

Dear Wanderer

Much Thanks for your input and as per @fpy suggestion, I have to use two counter one for base/source array and other for resultant / destination array.

Yes, as my present requirement LO Basic is good for me. But, I will learn python eventually.

Yours Sincerely
Muhammad Ali

[LO_user4]

Dear @karolus

Much Thanks for your input, this inbuilt function is also very Good Option.

I will following this in my upcoming works. I have recorded this method for future reference.

Much Much Thanks Brother.

Yours Sincerely
Muhammad Ali

[fpy]

@LO_user4

Ary_cln(i) = Ary(i)   '''input

first …

Ary_cln(i_cln) = Ary(i) 
i_cln = i_cln +1

then

ReDim Preserve  Ary_cln(i_cln-1)

---

then probably directly easier with
ScriptForge.Array service (SF_Array)
ScriptForge.Dictionary service

[LO_user4]

Dear @fpy

Much Thanks for your inputs and suggested code. I have revised my code as per your suggestion.

What I have observed is given below for others like me to understand.

The following code does complete job.

Sub test

Dim Ary(0 to 6) As String

Ary(0) = "empty"
Ary(1) = "cat"
Ary(2) = "empty"
Ary(3) = "empty"
Ary(4) = "dog"
Ary(5) = "rate"
Ary(6) = "hen"

Dim Ary_cln(0 to 6) As String   '''UBound should be equal to the base array

Dim ary_cnt_1 As Integer : ary_cnt_1 = 0  '''counter for final array with start value

For i = LBound(Ary) To UBound(Ary)
If Trim(Ary(i)) <> "empty" Then    ''' trim is use to remove space
Ary_cln(ary_cnt_1) = Ary(i)   '''input non black values in the clean array
ary_cnt_1 = ary_cnt_1+1

End If
Next i

ReDim Preserve Ary_cln(ary_cnt_1-1)       ''''this line removes the empty records of the array.

MsgBox UBound(Ary_cln)+1
For i = LBound(Ary_cln) to UBound(Ary_cln)
msg = msg & "index " & i & "--" & Ary_cln(i) & Chr(10)
Next i
MsgBox msg, 0, "Array Items"

End Sub

Refer below snapshot for the destination array.

If we comment out the following line.

''ReDim Preserve Ary_cln(ary_cnt_1-1)      

then it can be seen that resultant array has some empty records in the end. Refer the following snapshot.

So this is very good point highlighted by @fyp.

After finalization of arrays, use this command to remove the blank records of the array.

Also, I will also do check the following links, as they are also helpful and resource full.

ScriptForge.Array service (SF_Array)
ScriptForge.Dictionary service

In the end, I consider this query resolved. Thanks to all the Team members for your support and suggestion.

I pray for your health and safety.

Yours Sincerely
Muhammad Ali

[karolus]

@LO_user4

If Trim(Ary(i)) <> "empty" Then

your code removes exclusively the string »empty« from some array regardless if its repeated or not, but NOT any repeated entry, as your headline implies!