ExcelBanter - Return unique fields only

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Return unique fields only - but not a filter? (https://www.excelbanter.com/excel-worksheet-functions/40974-return-unique-fields-only-but-not-filter.html)

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

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.

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

Cheers guys - problem solved. I think I need to read up on array
formulas.

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

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

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

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