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 help773

[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

[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.

[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.

[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.

[snb]

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

In the codemodule of sheet 'Report'

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