Return unique fields only - but not a filter?

[Matt]

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

 

[Harlan Grove]

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$10000,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.

 

[Domenic]

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,COUNTIF($C$1:C1,A2:$A$10),
0)),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Matt]

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

 

[washdcjohn]

how do I do this referrencing data from a different worksheet?

 

[washdcjohn]

restated with more clarification;

How do I referrence source data on another worksheet where the data is
defined in a named range?

 

[Domenic]

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!

 

[Ron Rosenfeld]

restated with more clarification;

How do I referrence source data on another worksheet where the data is
defined in a named range?

You could download Longre's free morefunc.xll add-in from
http://xcell05.free.fr/ and use the UNIQUEVALUES function.



--ron