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

 

[kvsrinivasamurthy]

This will copy data from Sheet3 and give desired result in Supples Wise sheet range ("A15"). Change the ranges and sheet names as required.

Sub SortRemoveDuplicates()
With Sheets("Supplier Wise").Range("A15")
.CurrentRegion.Clear
Sheets("Sheet3").Range("A1").CurrentRegion.Copy Sheets("Supplier Wise").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

 

[HighAndWilder]

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

The named ranges reference the source data and not the data that has been copied to remove the duplicates from.

Why do you want to use named ranges?

 

[hrayani]

The named ranges reference the source data and not the data that has been copied to remove the duplicates from.

Why do you want to use named ranges?

Sorry, I did not understand your point
Why shouldn't I used a named range ??
the data is being used on lot of other sheets as well to generate different reports

 

[HighAndWilder]

Sorry, I did not understand your point
Why shouldn't I used a named range ??
the data is being used on lot of other sheets as well to generate different reports

If data with named ranges is copied to another sheet then the copied data does not have any associated named ranges.

This procedure saves the source data into an array, sorts the data and removes the duplicates using your existing named ranges and then reinstates the
oiginal data from the array.

It clears the existing data on the 'Supplier Wise' worksheet before it is replaced with the new data,

Test it on a copy of your data.

Public Sub subSortRemoveDuplicatesV2()
Dim Ws As Worksheet
Dim arr() As Variant

  ' ***************** Change the sheet name as appropriate. *****************
  Set Ws = Sheets("Sheet3")
  
  arr = Ws.Range("A1").CurrentRegion
        
  With Ws.Range("A1").CurrentRegion
        
    .Sort Key1:=Range("orders_article"), Order1:=xlDescending, _
      Key2:=Range("orders_quality"), Order2:=xlDescending, _
      Key3:=Range("orders_unit"), Order3:=xlDescending, Header:=xlYes
    
    .RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes
   
  End With
  
  Sheets("Supplier Wise").Range("B11").CurrentRegion.ClearContents
    
  With Ws.Range("A1").CurrentRegion
    Sheets("Supplier Wise").Range("B11").Resize(.Rows.Count, .Columns.Count).Value = .Value
    .ClearContents
  End With
        
  Ws.Range("A1").Resize(UBound(arr), 3).Value = arr
    
End Sub

 

[kvsrinivasamurthy]

This will remove duplicates and then Puts the data In Supplier Wise sheet and sorts the data.

Sub SortRemoveDuplicates()
Dim Ary, T&
Dim Dic As Object
Ary = Sheets("Sheet3").Range("A1").CurrentRegion
Set Dic = CreateObject("Scripting.Dictionary")
With Dic
For T = 2 To UBound(Ary, 1)
If Not .exists(Ary(T, 1) & Ary(T, 2) & Ary(T, 3)) Then
.Item(Ary(T, 1) & Ary(T, 2) & Ary(T, 3)) = Array(Ary(T, 1), Ary(T, 2), Ary(T, 3))
End If
Next T
End With

With Sheets("Supplier Wise").Range("A15")
.CurrentRegion.Clear
.Resize(1, 3) = Array(Ary(1, 1), Ary(1, 2), Ary(1, 3))
.Offset(1, 0).Resize(Dic.Count, 3) = WorksheetFunction.Index(Dic.items, 0, 0)
.CurrentRegion.Sort Key1:=.Offset(1, 0), Order1:=xlDescending, Key2:=.Offset(1, 1), Order2:=xlDescending, Key3:=.Offset(1, 2), Order3:=xlDescending, Header:=xlYes
End With

End Sub