Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Return unique fields only - but not a filter?
I think my issue is best displayed with an example:
Here's my rows of data: Column A Bob Dave Dave Eric Simon Simon Simon Simon Tom etc.... Obviously there's other columns of data associated with each person. Is there a formula that can return the following: Bob Dave Eric Simon Tom etc... i.e. only the unique fields from the array? I want to return the unique fields on a different sheet and then use sumif on them to get the required results. I can't realy use filters/advanced filters etc as I want the user to only have to paste in his appropriate data and then move to the other sheet and see his results. Can anyone help? Thanks Matt |
#2
|
|||
|
|||
Matt wrote...
I think my issue is best displayed with an example: Here's my rows of data: Column A Bob Dave Dave Eric Simon Simon Simon Simon Tom etc.... .... I'll assume this is in Sheet1!A1:A10000. Is there a formula that can return the following: Bob Dave Eric Simon Tom etc... .... Enter the following formulas in Sheet2. A1: =Sheet1!A1 A2 [array formula]: =IF(SUMPRODUCT(COUNTIF(A$1:A1,Sheet1!A$1:A$10000)) <COUNTA(Sheet1!A$1:A$10000),INDEX(Sheet1!A$1:A$100 00,MATCH(0, COUNTIF(A$1:A1,Sheet1!A$1:A$10000),0)),"") Select A2 and fill down as needed, worst case into Sheet2!A3:A10000. |
#3
|
|||
|
|||
Assuming that A2:A10 contains your data...
B2: =SUMPRODUCT((A2:A10<"")/COUNTIF(A2:A10,A2:A10&"")) C2, copied down: =IF(ROWS($C$1:C1)<=$B$2,INDEX(A2:$A$10,MATCH(0,COU NTIF($C$1:C1,A2:$A$10), 0)),"") ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article . com, "Matt" wrote: I think my issue is best displayed with an example: Here's my rows of data: Column A Bob Dave Dave Eric Simon Simon Simon Simon Tom etc.... Obviously there's other columns of data associated with each person. Is there a formula that can return the following: Bob Dave Eric Simon Tom etc... i.e. only the unique fields from the array? I want to return the unique fields on a different sheet and then use sumif on them to get the required results. I can't realy use filters/advanced filters etc as I want the user to only have to paste in his appropriate data and then move to the other sheet and see his results. Can anyone help? Thanks Matt |
#4
|
|||
|
|||
Cheers guys - problem solved. I think I need to read up on array
formulas. |
#5
|
|||
|
|||
Return unique fields only - but not a filter?
how do I do this referrencing data from a different worksheet?
"Domenic" wrote: Assuming that A2:A10 contains your data... B2: =SUMPRODUCT((A2:A10<"")/COUNTIF(A2:A10,A2:A10&"")) C2, copied down: =IF(ROWS($C$1:C1)<=$B$2,INDEX(A2:$A$10,MATCH(0,COU NTIF($C$1:C1,A2:$A$10), 0)),"") ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article . com, "Matt" wrote: I think my issue is best displayed with an example: Here's my rows of data: Column A Bob Dave Dave Eric Simon Simon Simon Simon Tom etc.... Obviously there's other columns of data associated with each person. Is there a formula that can return the following: Bob Dave Eric Simon Tom etc... i.e. only the unique fields from the array? I want to return the unique fields on a different sheet and then use sumif on them to get the required results. I can't realy use filters/advanced filters etc as I want the user to only have to paste in his appropriate data and then move to the other sheet and see his results. Can anyone help? Thanks Matt |
#6
|
|||
|
|||
Return unique fields only - but not a filter?
restated with more clarification;
How do I referrence source data on another worksheet where the data is defined in a named range? "Domenic" wrote: Assuming that A2:A10 contains your data... B2: =SUMPRODUCT((A2:A10<"")/COUNTIF(A2:A10,A2:A10&"")) C2, copied down: =IF(ROWS($C$1:C1)<=$B$2,INDEX(A2:$A$10,MATCH(0,COU NTIF($C$1:C1,A2:$A$10), 0)),"") ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article . com, "Matt" wrote: I think my issue is best displayed with an example: Here's my rows of data: Column A Bob Dave Dave Eric Simon Simon Simon Simon Tom etc.... Obviously there's other columns of data associated with each person. Is there a formula that can return the following: Bob Dave Eric Simon Tom etc... i.e. only the unique fields from the array? I want to return the unique fields on a different sheet and then use sumif on them to get the required results. I can't realy use filters/advanced filters etc as I want the user to only have to paste in his appropriate data and then move to the other sheet and see his results. Can anyone help? Thanks Matt |
#7
|
|||
|
|||
Return unique fields only - but not a filter?
First, where ever you enter your formula make sure that there's an empty
cell above it. So, for example, if you were going to generate your list in Column B... 1) Leave B1 empty 2) Enter the following formula in B2 and copy down: =IF(OR(COUNTIF($B$1:B1,List)=0),INDEX(List,MATCH(0 ,COUNTIF($B$1:B1,List), 0)),"") ....where 'List' is your named range. Adjust this reference accordingly. The formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article , "washdcjohn" wrote: restated with more clarification; How do I referrence source data on another worksheet where the data is defined in a named range? "Domenic" wrote: Assuming that A2:A10 contains your data... B2: =SUMPRODUCT((A2:A10<"")/COUNTIF(A2:A10,A2:A10&"")) C2, copied down: =IF(ROWS($C$1:C1)<=$B$2,INDEX(A2:$A$10,MATCH(0,COU NTIF($C$1:C1,A2:$A$10), 0)),"") ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article . com, "Matt" wrote: I think my issue is best displayed with an example: Here's my rows of data: Column A Bob Dave Dave Eric Simon Simon Simon Simon Tom etc.... Obviously there's other columns of data associated with each person. Is there a formula that can return the following: Bob Dave Eric Simon Tom etc... i.e. only the unique fields from the array? I want to return the unique fields on a different sheet and then use sumif on them to get the required results. I can't realy use filters/advanced filters etc as I want the user to only have to paste in his appropriate data and then move to the other sheet and see his results. Can anyone help? Thanks Matt |
#8
|
|||
|
|||
Return unique fields only - but not a filter?
On Wed, 19 Oct 2005 15:33:04 -0700, "washdcjohn"
wrote: restated with more clarification; How do I referrence source data on another worksheet where the data is defined in a named range? "Domenic" wrote: Assuming that A2:A10 contains your data... B2: =SUMPRODUCT((A2:A10<"")/COUNTIF(A2:A10,A2:A10&"")) C2, copied down: =IF(ROWS($C$1:C1)<=$B$2,INDEX(A2:$A$10,MATCH(0,COU NTIF($C$1:C1,A2:$A$10), 0)),"") ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Hope this helps! In article . com, "Matt" wrote: I think my issue is best displayed with an example: Here's my rows of data: Column A Bob Dave Dave Eric Simon Simon Simon Simon Tom etc.... Obviously there's other columns of data associated with each person. Is there a formula that can return the following: Bob Dave Eric Simon Tom etc... i.e. only the unique fields from the array? I want to return the unique fields on a different sheet and then use sumif on them to get the required results. I can't realy use filters/advanced filters etc as I want the user to only have to paste in his appropriate data and then move to the other sheet and see his results. Can anyone help? Thanks Matt You could download Longre's free morefunc.xll add-in from http://xcell05.free.fr/ and use the UNIQUEVALUES function. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return a unique value | Excel Worksheet Functions | |||
How do I set up filter for page fields in pivot table? | Excel Discussion (Misc queries) | |||
Inconsistent Results: Advanced Filter Unique Records Only | Excel Discussion (Misc queries) | |||
How do I return the unique entries from a column to a listbox | Excel Worksheet Functions | |||
filter on color-filled fields | Excel Worksheet Functions |