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"


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

  • What version of Excel are you using?

    You may want to consider updating your profile to indicate that. If you are using Excel 365 a formula solution is possible with no VBA.

    Jeff


    Making the world a better place one fret at a time | | |·| |·| |·| |·| | |:| | |·| |·|

  • Your file has no code in it.


    This data is a bit strange. It is formatted as General but appears to be text since it is left-justified and some numbers have a leading 0. However, when I do an Excel sort, I get a numeric sort. Your example shows that you want a text sort.


    How did this data get into your Excel file?

    Jeff


    Making the world a better place one fret at a time | | |·| |·| |·| |·| | |:| | |·| |·|

  • 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,


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


    Thanks to All for the Support.

  • The code in your first post does not work in the file you attached so I am at a dead end.


    When I tried the sort approach you just showed, the sort order did not match the example you gave in the file. Also, the table in the Calculation sheet is Table2, not Table1 as shown in your latest code.

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

    And none of that is anywhere in the file you attached. So I really don't understand what you're doing.


    Glad you worked it out.

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


    Thank You All.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!