Create Unique List Automatically Updates

robinsn

New Member
Joined
Jul 14, 2010
Messages
13
Hello

I would like to create a unique list of names from a report. I can do it using the Advanced Filter option but I cannot get it to automatically update if I add a new name.

Is there a way in Excel so that the unique listed is automatically updated?


Thanks

Niall
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Hello

I would like to create a unique list of names from a report. I can do it using the Advanced Filter option but I cannot get it to automatically update if I add a new name.

Is there a way in Excel so that the unique listed is automatically updated?


Thanks

Niall

What is the name of the sheet housing the source data?
What is the current range?
Is it numeric or text?
 
Upvote 0
Hello

Thanks for the response.

The name of sheet with the data is called T&A Data

The current range is A2:O100000

The employee names are in Column O

The unique list is text
 
Upvote 0
Hello

Thanks for the response.

The name of sheet with the data is called T&A Data

The current range is A2:O100000

The employee names are in Column O

The unique list is text

The following formula system won't be efficient, given the size of the target range...

Define SRange using Formulas | Name Manager as referring to:
Rich (BB code):
='T&A Data'!$O$2:INDEX('T&A Data'!$O:$O,MATCH(REPT("z",255),'T&A Data'!$O:$O))

Q1, control+shift+enter, not just enter:
Rich (BB code):
=SUM(IF(FREQUENCY(IF(SRange<>"",MATCH(SRange,SRange,0)),
   ROW(SRange)-ROW(INDEX(SRange,1))+1),1))

In Q2 enter: #DList# (from Distinct List)

Q3, control+shift+enter, not just enter, and copy down:
Rich (BB code):
=IF(ROWS($Q$3:Q3)<=$Q$1,INDEX(SRange,
  MODE(IF(SRange<>"",IF(ISNA(MATCH(SRange,Q$2:Q2,0)),
   MATCH(SRange,SRange,0)*{1,1})))),"")

If the performance is an issue, try using SQL on which you can find
some posts by Fazza at this board.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

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
Back
Top