VBA Macro To Create A New Column Of Unique Values From Another Column Of Duplicates

[ketats1]

Hello,

I would need your help on he following please.

in tab : report- I'll choose the country.
in tab : Extract ESr: data will be changed accordingly to the selection.

and data will be cleaned in column T-U- V- W.

I need kind of macro in Column w (or formula), to extract unique value from column V. Without duplicates,

This will allow me to make a reference in Tab report to put this code into a table without duplicates.

PS: Iforget to mentioned that i need the macro to run each time we change the selection of the country.



Thank you for your help01-Manual Aspiration-Advance-test.xlsx

[bakerman]

Try this one

Sub NoDupes()
    Dim sq() As Variant
    With Sheets("Extract ESR")
        sn = .Range("V2:V" & .Cells(Rows.Count, 22).End(xlUp).Row)
    End With
    On Error Resume Next
    With New Collection
        For j = 1 To UBound(sn)
            .Add sn(j, 1), CStr(sn(j, 1))
        Next
        ReDim Preserve sq(.Count)
        For i = 1 To .Count
            sq(i - 1) = .Item(i)
        Next
    End With
    On Error GoTo 0
    Sheets("Extract ESR").Range("W2").Resize(UBound(sq)) = WorksheetFunction.Transpose(sq)
End Sub

[ketats1]

Hello,

I'm trying to understand a little about macro.

I tried your proposition, but i see that the macro is not running automatically. Do you have any explanation please?

It should run every time data in column V change.

Thank you for helping me.

[Admin]

Hi

Another option,

In Worksheet (Report) module. Right click on tab 'Report' > view code > paste the code there.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    kTest
    
End Sub

in a standard module

Option Explicit

Sub kTest()
    
    Dim d, k, w, x, i As Long, n As Long, Country As String
    Dim c As String, s As String, m As String
    
    Set d = CreateObject("scripting.dictionary")
    
    w = Worksheets("Extract ESR").Range("a1").CurrentRegion.Resize(, 11).Value2
    
    Country = Worksheets("Report").Range("c1")
    ReDim k(1 To UBound(w, 1), 1 To 1)
    
    For i = 2 To UBound(w, 1)
        c = Country & w(i, 4) & w(i, 11)
        If c = w(i, 1) Then
            If Not d.exists(c) Then
                If InStr(1, m, w(i, 6)) = 0 Then
                    n = n + 1
                    k(n, 1) = w(i, 6)
                    m = m & "," & w(i, 6)
                End If
                d.Item(c) = w(i, 6)
            Else
                If InStr(1, m, w(i, 6)) = 0 Then
                    n = n + 1
                    k(n, 1) = w(i, 6)
                    m = m & "," & w(i, 6)
                End If
            End If
        End If
    Next
    If n Then
        With Worksheets("Extract ESR").Range("w2").Resize(UBound(w, 1))
            .ClearContents
            If Not .NumberFormat = "@" Then .NumberFormat = "@"
            .Value = k
        End With
    End If
    
End Sub

Now while you change the country, the ids will be updated automatically. You don't need those formulas in columns R,S,T,U and V.

[ketats1]

This looks perfect.

but the problem, the macro is not running automatically.

I need to select "run macro", each time the country change.
And my problem is: this report will be used by others people, and for their analysis, data need to be automatically cleaned.
Is there any way to make it automatic

[ketats1]

No, It works

I would like to thank you very much.

Really it's helpful

[ketats1]

Hello,

I have a question: can you tell me on which column the macro refer for "region"?? because in my file Portugal & spain shoul appear under Iberia, and if i put Iberia in the selection : c1 (worksheet report). The marco stops working.
If i need to modify the value of region, where can i do it??

Thank you in advance for your help.

[Excel Fox]

The post by Admin is meant for the worksheet change event to be triggered, and then the kTest procedure to be run. If you want to limit the trigger to a change in column V only, you could use

Private Sub Worksheet_Change(ByVal Target As Range)
    
if Not Application.Intersection(target, Me.Range("V:V")) Is Nothing Then
    kTest
End If
    
End Sub

[ketats1]

Hello,

I have a question: can you tell me on which column the macro refer for "region"?? because in my file Portugal & spain shoul appear under Iberia, and if i put Iberia in the selection : c1 (worksheet report). The marco stops working.
If i need to modify the value of region, where can i do it??

Thank you in advance for your help.

[Admin]

Hello,
...
I have a question: can you tell me on which column the macro refer for "region"??
If i need to modify the value of region, where can i do it??....

In the macro it only refers Col D and Col K which refers as w(i, 4) and w(i, 11) respectively.

Also write the following line before kTest in the worksheet module code.

If Target.Address(0, 0) <> "C1" Then Exit Sub

this restrcits the macro to fire when C1 changes.