How to remove duplicates in array in VBA and concatenate them

Question

I have an array an VBA strname=[English,science,Social,English,Social,science,science,Social,English,,,]

I want to remove duplicates and empty values in this array and concatenate them. Expected Output : English;science;Social

I tried with looping logic but it doesnt work

For i=0 to 10
 if strname[i] <> "" then
   if strname[i]= strname[i+1] then 
       tempstr=strname[i]
   end i
 end if
next

here it will check 0 with 1 , 1 with 2 like that.Am trying for proper solution

Have you done any research? There are many examples eg https://stackoverflow.com/questions/3017852/vba-get-unique-values-from-array — SJR, Feb 13 '23 at 09:02

FaneDuru · Accepted Answer · 2023-02-13T13:03:41.200

2

Please, try the next way:

Sub removeArrDuplAndEmpty()
   Dim x As String, arr, i As Long, dict As Object
   
   x = "English,science,Social,English,Social,science,science,Social,English,,,"
   Set dict = CreateObject("Scripting.Dictionary")
   arr = Split(x, ",") 'extract the array
   
   For i = 0 To UBound(arr)
        If arr(i) <> "" Then dict(arr(i)) = 1 'create unique keys for non blank array elements
   Next 

   arr = dict.keys 'place back the dictionary keys in the initial array
   Debug.Print Join(arr, ";") 'only to visually see the result
End Sub

The next version processes the string as you show it in your question:

Sub removeArrDuplAndEmptyBis()
   Dim x As String, arr, i As Long, dict As Object
   
   x = "[English,science,Social,English,Social,science,science,Social,English,,,]"
   Set dict = CreateObject("Scripting.Dictionary")
   arr = Split(left(Mid(x, 2), Len(Mid(x, 2)) - 1), ",")
   
   For i = 0 To UBound(arr)
        If arr(i) <> "" Then dict(arr(i)) = 1
   Next i
   arr = dict.keys
   Debug.Print Join(arr, ",")
End Sub

This is what you want? If not, please show an example of your real string and how it must look after processing...

edited Feb 13 '23 at 13:03

answered Feb 13 '23 at 09:28

FaneDuru

38,298
4
19
27

Why X was declared in "".I am getting values in [ ] ....not in "" It will work only when we declare value as dict – Yes_par_row Feb 13 '23 at 12:51
@Yes_par_row I cannot get you... 1. Do you have a string or an array? I used a string (exactly what you show in your question), to split it in an array and iterate/work by array elements. Is your string to be processed between `[` `]` characters? Do you want it returned also between these characters? Please, better explain how **exactly** your string to be processed looks and how **exactly** to be returned. – FaneDuru Feb 13 '23 at 12:58
@Yes_par_row Please, test the second version and send some feedback. – FaneDuru Feb 13 '23 at 13:04
"ActiveX Component Can’t Create Object"-Am facing this error version : windows 10 – Yes_par_row Feb 13 '23 at 17:49
@Yes_par_row This is not possible. Did it create the object in the first version and doesn't now? Do you have AnyDesk installed? If yes and you accept a connection, i would like to see what's happening in your environment. Did you test the sub **exactly as it is**? – FaneDuru Feb 13 '23 at 17:55
Yes It was worked in local.When i run the exact code in client machine,it was throwing an error – Yes_par_row Feb 13 '23 at 17:58
@Yes_par_row Isn't it a MAC computer? – FaneDuru Feb 13 '23 at 18:02
@Yes_par_row Can you add a reference to 'Microsoft Scripting Runtime'? Do yo see it between `Availalble References`? – FaneDuru Feb 13 '23 at 18:06
@Yes_par_row May I connect to the respective computer? – FaneDuru Feb 13 '23 at 18:23
@FaneDuru- It was resolved . I used below method to declare Dictionary. DIM Dict as Scripting.Dictionary Set Dict=New Scripting.Dictionary – Yes_par_row Feb 14 '23 at 05:01

How to remove duplicates in array in VBA and concatenate them

1 Answers1