Pulling a Summary List from a Larger List

[Stephen - Dallas]

Here's the scene -
List 1:
Joe
Joe
Joe
Jill
Jill
Jake
Jane
Jane

I want a list that will summarize the names such that I will see each name
only once (same premise as the Autofilter); i.e. desired result:
Summary:
Joe
Jill
Jake
Jane

In addition, if I add Jack to List 1....-
List 1:
Joe
Joe
Joe
Jill
Jill
Jake
Jane
Jane
Jack
Jack
Jack
Jack

.....the name "Jack" will automatically be added to the Summary list:
Summary:
Joe
Jill
Jake
Jane
Jack

Thank you to anyone that can help.

 

[Ron Coderre]

Here are 2 options.

For data in Cells A1:A100, with A1: List1

1)Advanced Filter Example:
B1: List1
<Data><Filter><Advanced Filter>
ChecK; copy to another location
Check: Unique records only
List range: $A$1:$A$100
Copy to: $B$1
Click the [OK] button

That will create a list of unique List1 names

OR
2)Formula alternative:
B1: Summary
B2:
=IF(SUMPRODUCT(($A$2:$A$100<>"")*ISNA(MATCH($A$2:$A$100,$B$1:B1,0)))<>0,INDEX($A$2:$A$100,MATCH(TRUE,ISNA(IF(ISBLANK($A$2:$A$100),FALSE,MATCH($A$2:$A$100,$B$1:$B1,0))),0),1),"")

Note: For that array formula, hold down [Ctrl] and [Shift] when you press
[Enter].

Copy B2 and paste into B3 and down as far as you need
That formula will also create a list of unique List1 names

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Stephen - Dallas]

Thanks, Ron.
Advance Filter worked out great!

Here are 2 options.

For data in Cells A1:A100, with A1: List1

1)Advanced Filter Example:
B1: List1
<Data><Filter><Advanced Filter>
ChecK; copy to another location
Check: Unique records only
List range: $A$1:$A$100
Copy to: $B$1
Click the [OK] button

That will create a list of unique List1 names

OR
2)Formula alternative:
B1: Summary
B2:
=IF(SUMPRODUCT(($A$2:$A$100<>"")*ISNA(MATCH($A$2:$A$100,$B$1:B1,0)))<>0,INDEX($A$2:$A$100,MATCH(TRUE,ISNA(IF(ISBLANK($A$2:$A$100),FALSE,MATCH($A$2:$A$100,$B$1:$B1,0))),0),1),"")

Note: For that array formula, hold down [Ctrl] and [Shift] when you press
[Enter].

Copy B2 and paste into B3 and down as far as you need
That formula will also create a list of unique List1 names

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro

Here's the scene -
List 1:
Joe
Joe
Joe
Jill
Jill
Jake
Jane
Jane

I want a list that will summarize the names such that I will see each name
only once (same premise as the Autofilter); i.e. desired result:
Summary:
Joe
Jill
Jake
Jane

In addition, if I add Jack to List 1....-
List 1:
Joe
Joe
Joe
Jill
Jill
Jake
Jane
Jane
Jack
Jack
Jack
Jack

....the name "Jack" will automatically be added to the Summary list:
Summary:
Joe
Jill
Jake
Jane
Jack

Thank you to anyone that can help.