Pulling a Summary List from a Larger List
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. |
Pulling a Summary List from a Larger List
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" wrote: 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. |
Pulling a Summary List from a Larger List
Thanks, Ron.
Advance Filter worked out great! "Ron Coderre" wrote: 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" wrote: 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. |
All times are GMT +1. The time now is 08:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com