Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create a sorted summary list of counted species | Excel Discussion (Misc queries) | |||
Check 2 different list get associated value from a column | Excel Worksheet Functions | |||
Filterered list to new worksheet | Excel Worksheet Functions | |||
Extracting Values on one list and not another | Excel Discussion (Misc queries) | |||
Summary List?? | Excel Discussion (Misc queries) |