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

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 helpAttachment 773

Try this one

Code:

---

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

---

Hi

Another option,

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

Code:

---

Option Explicit

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

---

in a standard module

Code:

---

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.

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

No, It works :)

I would like to thank you very much.

Really it's helpful :)

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.

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.

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

Code:

---

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

---

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.

Quote:

---

Originally Posted by ketats1

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.

Code:

---

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

---

this restrcits the macro to fire when C1 changes.

Basically a oneliner suffices, provided the 'name in cell R1 contains no spaces e.g 'concatselection'.

In the codemodule of sheet 'Report'

Code:

---

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$1" Then Sheets("Extract ESR").UsedRange.Columns(18).AdvancedFilter 2, , Sheets("Extract ESR").Cells(1, 23), True
End Sub

---