VB Code to find unique names

**rizmomin** · 10-16-2016, 02:16 PM

Hello:

Please refer to attached file.
I have names as shown in Column G Cell G3 down.
I need VB Code to do the following.
1st replace text "SUPNA" by ""
2nd Find unique names from list and paste at cell M3.

Let me know if you have any questions.
Thanks.

Riz

**GC Excel** · 10-16-2016, 02:32 PM

Hello Riz,

Here's suggestion :

Sub UniqueNames()
   Dim str As String
   Dim ar, i As Integer
   
   ar = Range("G3:G" & Range("G" & Rows.Count).End(xlUp).Row).Value
   With CreateObject("Scripting.dictionary")
      For i = 1 To UBound(ar, 1)
         str = Replace(ar(i, 1), "SUPNA", "")
         If Not .exists(str) Then .Add str, str
      Next i
      Range("M3").Resize(.Count, 1) = Application.Transpose(.keys)
   End With
End Sub

**TMS** · 10-16-2016, 03:21 PM

I'd be inclined to use an Advanced Filter, something like:

Sub sExtractUniqueNames()
Dim lLR As Long
Application.ScreenUpdating = False
lLR = Range("G" & Rows.Count).End(xlUp).Row
Columns("G:G").Replace What:=" SUPNA", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
Range("G2").FormulaR1C1 = "Name"
Columns("M:M").EntireColumn.Clear
Range("G2:G" & lLR).AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=Range("M2"), Unique:=True
Columns("M:M").EntireColumn.AutoFit
Range("G2").Value = ""
Range("M2").Value = ""
Application.ScreenUpdating = True
End Sub

**rizmomin** · 10-16-2016, 03:33 PM

Hello GC_Excel and TMS:

Both solution will work great for me.
Now i need code to add the unique list cell M3 down to Sheet2 at Q3.
Thanks a lot

Riz

**TMS** · 10-16-2016, 03:35 PM

Why don't you just put the list straight out to Sheet2?

**TMS** · 10-16-2016, 03:42 PM

Sub sExtractUniqueNames()
Dim lLR As Long
Application.ScreenUpdating = False
lLR = Range("G" & Rows.Count).End(xlUp).Row
Columns("G:G").Replace What:=" SUPNA", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
Range("G2").FormulaR1C1 = "Name"
Columns("M:M").EntireColumn.Clear
Range("G2:G" & lLR).AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=Range("M2"), Unique:=True
Columns("M:M").EntireColumn.AutoFit
Range("G2").Value = ""
Range("M2").Value = ""

lLR = Range("M" & Rows.Count).End(xlUp).Row
With Sheets("Sheet2")
    .Columns("Q:Q").EntireColumn.Clear
    Sheets("sheet1").Range("M3:M" & lLR).Copy .Range("Q3")
End With
Application.ScreenUpdating = True
End Sub

**GC Excel** · 10-16-2016, 04:10 PM

Like this ?

Sub UniqueNames()
   Dim str As String
   Dim ar, i As Integer
   
   ar = Range("G3:G" & Range("G" & Rows.Count).End(xlUp).Row).Value
   With CreateObject("Scripting.dictionary")
      For i = 1 To UBound(ar, 1)
         str = Replace(ar(i, 1), "SUPNA", "")
         If Not .exists(str) Then .Add str, str
      Next i
      Range("M3").Resize(.Count, 1) = Application.Transpose(.keys)
      With Sheets("Sheet2")
           .Range("Q3").resize(10000,1).Clear    'Clear 10000 rows 
           .Range("Q3").Resize(.Count, 1) = Application.Transpose(.keys)
      End With
   End With
End Sub

**rizmomin** · 10-16-2016, 05:11 PM

Hello TMS and GC"

Works for me....Thanks a lot.

Riz

**TMS** · 10-16-2016, 05:37 PM

You're welcome. Thanks for the rep.

VB Code to find unique names

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VB Code to find unique names

Re: VB Code to find unique names

Re: VB Code to find unique names

Re: VB Code to find unique names

Re: VB Code to find unique names

Re: VB Code to find unique names

Re: VB Code to find unique names

Re: VB Code to find unique names

Re: VB Code to find unique names

Thread Information

Users Browsing this Thread

Similar Threads

Code to find Unique Names

VB Code to find Unique Names

[SOLVED] Need vba code to make sum and average of row depends upon unique names in col A

vba find unique names in multiple columns

Find unique names in certain range.

Excel 2007 - Formula or VBA to find unique names and return values?

[SOLVED] Find unique names and count their status

Bookmarks

Bookmarks

Posting Permissions