Create unique list from column in another worksheet

[mcgee1505]

Hi Excel World,

I have a worksheet (Sheet1) and in column AY, there is a list of names with many duplicates. I want to have a copy of the list in another worksheet (Sheet2) without the duplicates but also for this list to be dynamic so if more names are added to sheet1, they appears in my list insheet 2. Can this be done in a formula or does it need VBA?

Thanks in advance

 

[Fluff]

How about

=UNIQUE(FILTER(Sheet1!AY2:AY1000,Sheet1!AY2:AY1000<>""))

 

[mcgee1505]

Tried this but it didn't work. The example worksheet attached is the main worksheet.

Dynamic List Test.xlsx
																							W																											AX	AY
1																							Revision																											Column1	Assigned to
2																							A																												C Barnes
3																							A																												D Smith
4																							B																												V Wales
5																							A																												C Barnes
6																							C																												M Berry
7																							D																												G Patel
8																							A																												C Barnes
9																							B																												D Smith
10																							A																												C Barnes
11																							A																												M Berry
12																							F																												M Berry
13																							A																												D Smith
14																							A																												G Patel
15																							A																												C Barnes
16																							B																												V Wales
17																							B																												M Berry
18																							A																												M Berry
19																							A																												G Patel
20																							A																												V Wales
21																							A																												D Smith
Sheet1

From Sheet1 column AY to Sheet2 column A is where I want the unique dynamic list to go...

Dynamic List Test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Owner of Documents
2					DataPrep
3	Owner	Count	Show		Owner	Count
4	C Barnes	5	1		C Barnes	5
5	D Smith	3	1		D Smith	3
6	V Wales	1	1		V Wales	1
7		0
8		0
9		0
10		0
11		0
12		0
13		0
14		0
15		0
16		0
17		0
18		0
19		0
20		0
21		0
22		0
23		0
24		9				9
25
Sheet2

Cell Formulas
Range	Formula
E4:E6	E4	=SORTBY(FILTER(A4:A23,C4:C23=1),FILTER(B4:B23,C4:C23=1),-1)
F4:F6	F4	=VLOOKUP(E4#,A4:B23,2,FALSE)
C4:C23	C4	=IF(B4>0,1,"")
B4:B23	B4	=COUNTIFS(Sheet1!$AY:$AY,A4,Sheet1!$W:$W,"A")
B24,F24	B24	=SUM(B4:B23)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
CountOwner	=Sheet2!$F$4#	F24
Owner	=Sheet2!$E$4#	F4

Ther sheet2 table has

 

[Fluff]

Tried this but it didn't work.

In what way didn't it work?

 

[mcgee1505]

Got it working now, it didn't 'spill' down the column initially.

Amazed as ever, thank you very much....

 

[Fluff]

You're welcome & thanks for the feedback.