Get Unique Value in Another Sheet in a Alphabetic Order

Hi, All.

Hope everyone is well.

I am using the below code to get unique values from one sheet to another by a command button. Now the fact is I want that unique value in a sort from in ascending order.

But failed to do it. Hope someone looks through my code and helps me to achieve the result.

I will really appreciate the kind help & concern of anyone.

Here In Name Manager of the workbook.

"Name: Extract, Value: Unique, Refers To: =Report!$W$9, Scope: Report"

Sub Get_Unique()
Dim Sh1 As Worksheet
    Dim rng As Range
    Dim Sh2 As Worksheet
    Set Sh1 = Sheets("Calculation")
    Set rng = Sh1.Range("P2000:P" & Sh1.Range("P1000").End(xlUp).Row)
    Set Sh2 = Sheets("Report")
    rng.Cells(1, 1).Copy Sh2.Cells(1, 1)
    rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sh2.Range("W9"), Unique:=True
End Sub

Thanks in Advance.

Thanks 6StringJazzer,

I am using office 13 in-home and 16 at my workstation.

No, I am not using office 365.

It will be more helpful If it can be solved by VBA.

For learning new things I am very eager. Will you show How a Formula work at this point?

I am attaching a sample file Time_Table.zip.

Hope better help.

Thanks and best regards.

Hi,

As answer of your question this data is related to other function and reference.It is made with two cell data combination to make it unique. In calculation sheet data is occurring several times. But in Report sheet it form as unique and shorted as ascending order. Yes this data is in text form and want sort as text form.

The code is given earlier. Used as command button in standard module.

Thanks and appreciate your help in this regard.

Hi,

You want to know how the data get into the P10:P1000

Here in W10 =CONCATENATE(B10,O10) and in B10 I have ID and O10 =IF(B10="","",MONTH(H10) & YEAR(H10))

Hope it works.

Thanks

Hi,

I am able to solve the problem. just adding the below code before End Sub.

    Range("Table1[[#Headers],[Unique]]").Select
    ActiveWorkbook.Worksheets("Report").ListObjects("Table1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Report").ListObjects("Table1").Sort.SortFields.Add _
        Key:=Range("Table1[Unique]"), SortOn:=xlSortOnValues, Order:=xlAscending _
        , DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Report").ListObjects("Table1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Thanks to All for the Support.

Hi, Here I attached my sample File Sample_Done.xlsm format to which I applied the above code to get the unique value in sort form.

Hope It will be easy to understand for others.

Sub Get_Unique()

    Dim Sh1 As Worksheet
    Dim rng As Range
    Dim Sh2 As Worksheet
    Set Sh1 = Sheets("Calculation")
    Set rng = Sh1.Range("P2000:P" & Sh1.Range("P1000").End(xlUp).Row)
    Set Sh2 = Sheets("Report")
    rng.Cells(1, 1).Copy Sh2.Cells(1, 1)
    rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sh2.Range("W9"), Unique:=True
    
    Range("Table1[[#Headers],[Unique]]").Select
    ActiveWorkbook.Worksheets("Report").ListObjects("Table1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Report").ListObjects("Table1").Sort.SortFields.Add _
        Key:=Range("Table1[Unique]"), SortOn:=xlSortOnValues, Order:=xlAscending _
        , DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Report").ListObjects("Table1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

End Sub

Thank You All.