VBA: Copy Unique values from table based on certain condition

[wjeil]

Hello,

I want to copy the Unique Order ID from A2:A to H2:H based on a certain condition. If it is a singlepick (column E "Yes") I should not be copied to H. The example should result in a list at H of 9 rows without Order 2.
I only want to do this on command so VBA in combination with a button would be preferred.

How can I do this via VBA?

Map2
	A	B	C	D	E	F	G	H
1	OrderID	SKU.ID	ColliAmount	LOC.ID	SINGLEPICK			ORDERS
2	Order 1	B104	3	H05.001	NO
3	Order 2	A15	1	H01.013	YES
4	Order 3	A31	4	H02.006	NO
5	Order 3	A8	2	H01.004	NO
6	Order 4	A21	4	H02.005	NO
7	Order 4	B132	1	H04.010	NO
8	Order 4	B15	1	H03.016	NO
9	Order 5	C73	2	H01.043	NO
10	Order 5	A28	1	H02.013	NO
11	Order 6	A49	3	H02.009	NO
12	Order 7	B57	2	H03.019	NO
13	Order 7	A36	1	H01.020	NO
14	Order 7	A6	3	H01.003	NO
15	Order 7	A7	7	H01.001	NO
16	Order 7	A16	7	H02.008	NO
17	Order 7	A21	3	H02.005	NO
18	Order 7	A41	2	H03.003	NO
19	Order 7	A8	2	H01.004	NO
20	Order 8	A39	2	H01.007	NO
21	Order 8	B12	1	H03.022	NO
22	Order 8	B186	1	H03.015	NO
23	Order 8	A26	1	H03.003	NO
24	Order 8	A42	3	H02.009	NO
25	Order 8	A21	2	H02.005	NO
26	Order 8	A18	2	H04.003	NO
27	Order 8	A35	1	H03.005	NO
28	Order 8	A16	3	H02.008	NO
29	Order 8	B118	1	H03.017	NO
30	Order 9	C26	2	H05.038	NO
31	Order 10	B123	1	H04.007	NO
32	Order 10	A46	2	H02.012	NO
33	Order 10	A88	2	H01.018	NO
Blad1

 

[Fluff]

How about

Sub wjeil()
   Dim Ary As Variant
   
   With Range("A2", Range("A" & Rows.count).End(xlUp))
      Ary = Evaluate("unique(filter(" & .Address & "," & .Offset(, 4).Address & "<>""Yes""))")
   End With
   With Range("H2")
      .Resize(10000).ClearContents
      .Resize(UBound(Ary)).Value = Ary
   End With
End Sub

 

[wjeil]

Thanks!! One question what if i want to paste it on another worksheet in A?

 

[Fluff]

You just need to change the range from H2, to wherever you want it.