# Create unique list from column in another worksheet

#### mcgee1505

##### New Member
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?

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=UNIQUE(FILTER(Sheet1!AY2:AY1000,Sheet1!AY2:AY1000<>""))``

#### mcgee1505

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

Dynamic List Test.xlsx
WAXAY
1RevisionColumn1Assigned to
2AC Barnes
4BV Wales
5AC Barnes
6CM Berry
7DG Patel
8AC Barnes
9BD Smith
10AC Barnes
11AM Berry
12FM Berry
14AG Patel
15AC Barnes
16BV Wales
17BM Berry
18AM Berry
19AG Patel
20AV Wales
Sheet1

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

Dynamic List Test.xlsx
ABCDEFGHIJKLMN
1Owner of Documents
2DataPrep
3OwnerCountShowOwnerCount
4C Barnes51C Barnes5
5D Smith31D Smith3
6V Wales11V Wales1
70
80
90
100
110
120
130
140
150
160
170
180
190
200
210
220
230
2499
25
Sheet2
Cell Formulas
RangeFormula
E4:E6E4=SORTBY(FILTER(A4:A23,C4:C23=1),FILTER(B4:B23,C4:C23=1),-1)
F4:F6F4=VLOOKUP(E4#,A4:B23,2,FALSE)
C4:C23C4=IF(B4>0,1,"")
B4:B23B4=COUNTIFS(Sheet1!\$AY:\$AY,A4,Sheet1!\$W:\$W,"A")
B24,F24B24=SUM(B4:B23)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
CountOwner=Sheet2!\$F\$4#F24
Owner=Sheet2!\$E\$4#F4

Ther sheet2 table has

#### mcgee1505

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

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

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
1
Views
127
Replies
2
Views
131
Replies
0
Views
58
Replies
16
Views
143
Replies
4
Views
77

1,127,343
Messages
5,624,120
Members
416,012
Latest member
rockermom59

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back