Excel Formula to Generate Unique Sorted List

[hrayani]

Dear All,

Hope you all are doing well
Please have a look at the sheet & let me know whether is it possible to achieve with formulas (I am using excel 2016)

The purpose is to have Unique Sorted Values in ascending order → By Column B, then by Column A, then By Column C with custom sort list as Set(s), Pc(s), Pair(s) & Dozen(s)

Book1
	A	B	C
1	ARTICLE	QUALITY	UNIT
2	Blanket	Polyester 125 GSM	Dozen(s)
3	Blanket	Polyester 125 GSM	Dozen(s)
4	Blanket	Polyester 125 GSM	Set(s)
5	Blanket	Polyester 125 GSM	Set(s)
6	Blanket	Polyester 125 GSM	Pc(s)
7	Fitted Sheet	CVC 90 GSM	Pair(s)
8	Fitted Sheet	CVC 90 GSM	Pair(s)
9	Fitted Sheet	CVC 90 GSM	Set(s)
10	Fitted Sheet	CVC 90 GSM	Pc(s)
11
12
13	DESIRED RESULT ↓
14	ARTICLE	QUALITY	UNIT
15	Fitted Sheet	CVC 90 GSM	Set(s)
16	Fitted Sheet	CVC 90 GSM	Pc(s)
17	Fitted Sheet	CVC 90 GSM	Pair(s)
18	Blanket	Polyester 125 GSM	Set(s)
19	Blanket	Polyester 125 GSM	Pc(s)
20	Blanket	Polyester 125 GSM	Dozen(s)
Sheet3

Regards,

Humayun

 

[alansidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Duplicates" = Table.Distinct(Source)
in
    #"Removed Duplicates"

 

[hrayani]

Thanks for the reply but I need an excel formula (If possible)

 

[hrayani]

Dear All,

It seems like it is not possible to achieve with a formula...

so, can anybody please provide a vba code pls ?

Regards,

Humayun

 

[kvsrinivasamurthy]

Try.

Sub SortRemoveDuplicates()
With Range("A1").CurrentRegion
.Sort Key1:=Range("A2"), Order1:=xlDescending, Key2:=Range("B2"), Order2:=xlDescending, Key3:=Range("C2"), Order3:=xlDescending, Header:=xlYes
.RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes
End With
End Sub

 

[hrayani]

Try.

Sub SortRemoveDuplicates()
With Range("A1").CurrentRegion
.Sort Key1:=Range("A2"), Order1:=xlDescending, Key2:=Range("B2"), Order2:=xlDescending, Key3:=Range("C2"), Order3:=xlDescending, Header:=xlYes
.RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes
End With
End Sub

Hello @kvsrinivasamurthy

Thanks for the reply but the code you provided just removes the duplicates and that too on the actual data. whereas I want a separate list to be generated starting cell A15 with the actual data remaining as it is... Can you pls go through the mini sheet I posted in my initial post

 

[Peter_SSs]

Like this?

Sub New_List()
  With Range("A" & Rows.Count).End(xlUp).Offset(4).Resize(Range("A" & Rows.Count).End(xlUp).Row, 3)
    .Value = Range("A1").CurrentRegion.Value
    .Sort Key1:=.Columns(2), Order1:=xlAscending, Key2:=.Columns(1), Order2:=xlAscending, Key3:=.Columns(3), Order3:=xlDescending, Header:=xlYes
    .RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes
  End With
End Sub

 

[HighAndWilder]

Hello @kvsrinivasamurthy

Thanks for the reply but the code you provided just removes the duplicates and that too on the actual data. whereas I want a separate list to be generated starting cell A15 with the actual data remaining as it is... Can you pls go through the mini sheet I posted in my initial post

Or this.

Sub SortRemoveDuplicates()
Dim lngStart As Long
Dim Ws As Worksheet

    Set Ws = ActiveSheet
    
    lngStart = Ws.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row + 5
    
    Ws.Range("A1").CurrentRegion.Copy Ws.Range("A" & lngStart)
    
    With Ws.Range("A" & lngStart).CurrentRegion
        
        .Sort Key1:=Range("A" & lngStart), Order1:=xlDescending, _
            Key2:=Range("B" & lngStart), Order2:=xlDescending, _
            Key3:=Range("C" & lngStart), Order3:=xlDescending, Header:=xlYes
    
        .RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes
    
    End With

End Sub

 

[hrayani]

Like this?

Sub New_List()
  With Range("A" & Rows.Count).End(xlUp).Offset(4).Resize(Range("A" & Rows.Count).End(xlUp).Row, 3)
    .Value = Range("A1").CurrentRegion.Value
    .Sort Key1:=.Columns(2), Order1:=xlAscending, Key2:=.Columns(1), Order2:=xlAscending, Key3:=.Columns(3), Order3:=xlDescending, Header:=xlYes
    .RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes
  End With
End Sub

Hi Peter,

Thanks for the reply

Is it possible to generate the list on some other sheet which is within the workbook → sheet name "Supplier Wise" starting from B11
& instead of looking at the Column A for Article, Column B for Quality & Column for Units it should look at the named ranges as "orders_article", "orders_quality" & "orders_unit"

Further, please note that the data I provided was a sample data but the actual data is very huge & I have noticed that the whole data is being copied and then the duplicates are been removed. I don't want this to happen as there is a lot of other things going on at the bottom of the sheet where I want the list to be generated..

Regards,

Humayun

 

[kvsrinivasamurthy]

Try.

Sub SortRemoveDuplicates()
With Range("A15")
.CurrentRegion.Clear
Range("A1").CurrentRegion.Copy Range("A15")
.CurrentRegion.Sort Key1:=.Offset(1, 0), Order1:=xlDescending, Key2:=.Offset(1, 1), Order2:=xlDescending, Key3:=.Offset(1, 2), Order3:=xlDescending, Header:=xlYes
.CurrentRegion.RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes
End With
End Sub