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.

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


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

  • 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

    Hope better help.

    Thanks and best regards.

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


    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,

    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.


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


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

    Edited once, last by 6StringJazzer ().

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