Remove duplicates, spaces from Column A and paste value to B1

[koskesh]

Hi, I need a fairly simple macro

The macro should

- look at column A

A
333
444

555
666

- remove duplicates
- remove spaces

And paste everything from column A into B1 and seperate them with a semicolon instead of space
333;444;555;666

Hope its clear

Thanks!!

 

[Chihiro]

I'd suggest you upload sample workbook, with data set that represent your actual data (i.e. desensitize data, but keep exactly the same data type and structure).

As well, this can easily be done using PowerQuery. Do you have access to it?

 

[koskesh]

Hi, thanks for the quick reply. Please see attached the workbook

 

[Chihiro]

Something like below. Assuming Windows machine.

Sub Demo()
Dim cel As Range

With CreateObject("Scripting.Dictionary")
    For Each cel In Range("B3:B" & Cells(Rows.Count, 2).End(xlUp).Row).Cells
        If Len(cel.Value) > 0 Then
            .Item(cel.Value) = 1
        End If
    Next
    [E3].Value = Join(.Keys, ";")
End With

End Sub