Create a list, removing duplicates, based off another list

[paun_shotts]

Hi,

I have a list of products on order with a supplier, the list is updated as new orders are placed with the supplier, this list will always have duplicate values as we order the same products on a weekly basis.
I want to create a summary list, and I want this list to have no duplicates in it. I want this list of unique values, created off the other list where all the duplicate values are. And would like the summary list to update automatically if a new product is ordered and added to the main list of products on order, so the new product will then show on the summary list as well.

At the moment, If I order a new product, not ordered before, I am adding that product code manually to the summary list.
Is this possible? Please help me.

 

[LouisT]

look up the "Unique" formula. That sounds like it could be your solution.

 

[Akbarov]

If you want VBA automation.

"TOP" ='Where you need unique values to be pasted
.Columns("A") = 'Column where unique values will be pasted / clear before new data will be added
Sheets("DATA").Range("M24:M2000") = Range where you have dublicate values.

Sub RemDubSt()
 With Sheets("TOP") 
    .Columns("A").ClearContents
   Sheets("DATA").Range("M24:M2000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheets("TOP").Columns("A"), Unique:=True
  End With
End Sub

 

[Peter_SSs]

look up the "Unique" formula. That sounds like it could be your solution.

The Unique function is not much use to somebody who is using Excel 2013.

@paun_shotts
Is this the sort of thing you are after?

22 08 17.xlsm
	A	B	C
1	Product		Unique List
2	Prod 1		Prod 1
3	Prod 4		Prod 4
4	Prod 3		Prod 3
5	Prod 24		Prod 24
6	Prod 15		Prod 15
7	Prod 6		Prod 6
8	Prod 4		Prod 5
9	Prod 5
10	Prod 3
11	Prod 4
12	Prod 5
13	Prod 6
14	Prod 4
15	Prod 4
16	Prod 5
17	Prod 6
18	Prod 4
19	Prod 5
20
Unique list

Cell Formulas
Range	Formula
C2:C18	C2	=IFERROR(INDEX($A$2:$A$1000,MATCH(0,INDEX(COUNTIF($C$1:C1,$A$2:$A$1000)+(A$2:A$1000=""),0),0)),"")