Create Unique List Automatically Updates

[robinsn]

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

 

[Akashwani]

Hi Niall,

Take a look here....

http://www.youtube.com/user/ExcelIsFun#p/search/7/IhuURsu0jdI

http://www.youtube.com/user/ExcelIsFun#p/search/15/0KnwjhjdbwY

Good luck

Ak

 

[Aladin Akyurek]

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?

 

[robinsn]

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

 

[Aladin Akyurek]

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:

='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:

=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:

=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.