Create unique list from column in another worksheet

mcgee1505

New Member
Joined
May 20, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

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
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=UNIQUE(FILTER(Sheet1!AY2:AY1000,Sheet1!AY2:AY1000<>""))
 

mcgee1505

New Member
Joined
May 20, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Tried this but it didn't work. The example worksheet attached is the main worksheet.

Dynamic List Test.xlsx
WAXAY
1RevisionColumn1Assigned to
2AC Barnes
3AD Smith
4BV Wales
5AC Barnes
6CM Berry
7DG Patel
8AC Barnes
9BD Smith
10AC Barnes
11AM Berry
12FM Berry
13AD Smith
14AG Patel
15AC Barnes
16BV Wales
17BM Berry
18AM Berry
19AG Patel
20AV Wales
21AD Smith
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
Joined
May 20, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Got it working now, it didn't 'spill' down the column initially.

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top