Return unique fields only - but not a filter?

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.

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!

In article <[email protected]>,
"Matt" <[email protected]> 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.

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.

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!

In article <[email protected]>,
"Matt" <[email protected]> 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.

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.

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!

In article <[email protected]>,
"Matt" <[email protected]> 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.

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.

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!

In article <[email protected]>,
"Matt" <[email protected]> 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.

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

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!

In article <[email protected]>,
"Matt" <[email protected]> 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.