Hi tonaveenv,
May be there are some other ways in excel to do this, but we may achieve your need with little help of VBA. Put the following code in the excel file where you have the list in Column A and run it. [Copy the code below, go to your excel file, hit Alt+F11, this will open the Visual Basic Window, hit Alt+I+M, a blank module will appear, hit Ctrl+V and to run the code hit F5]. If all goes fine, you should get the list according to your requirement at B1.
Please note that I am assuming your list at column A, does not have any header. If it does, make a little change in the formula in the code below:
instead of "=OFFSET($F$1,0,0,COUNTA($F:$F))", put "=OFFSET($F$1,1,0,COUNTA($F:$F))"
And also please note that I have used column F as helping column.
Sub MakeUniqueNameList()
Application.ScreenUpdating = False
Columns("A:A").Select
Range("A1:A12").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
"F1"), Unique:=True
Range("B1").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=OFFSET($F$1,0,0,COUNTA($F:$F))"
.IgnoreBlank = True
.InCellDropdown = True
End With
Columns("F:F").Select
With Selection.Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
End With
Range("B1").Select
Application.ScreenUpdating = True
End Sub
