Name Farming
 

I have a column with about 400 cells consisting from five to ten names. I'd like to display in a separate column all of those names once, and if possible, in a particular order, such as alphabetically.

EXAMPLE
A
1 Smith
2 Smith
3 Johnson
4 Davis
5 Johnson
6 Davis

FARMED

A
1 Davis
2 Johnson
3 Smith

And could this be done to be automatically updating, or would it only be a one-time function?

Thanks for your help!

Name Farming
 

Hi Christian,

Am Tue, 2 Apr 2013 11:57:32 +0000 schrieb Christian Michael:

EXAMPLE
A
1 Smith
2 Smith
3 Johnson
4 Davis
5 Johnson
6 Davis

FARMED

A
1 Davis
2 Johnson
3 Smith

And could this be done to be automatically updating, or would it only be
a one-time function?

if you have headers you can use advanced filter without duplicates. In
case of changes you have to do it again.
You can also do it with formula.
Your data in column A, then e.g. in C1:
=A1
In C2:
=IF(SUM(COUNTIF(A$1:A$99,C$1:C1))=SUM((A$1:A$99< "")*1),"",INDEX(A:A,MATCH(1,(COUNTIF(C$1:C1,A$1:A$ 99)=0)*(A$1:A$99<""),0)))
and enter this array formula with CTRL+Shift+Enter
and copy down


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi Christian Michael

Assuming your data in column A with a header.
To extract the unique values:
In B2 & copy down:
=IFERROR(INDEX($A$2:$A$7,MATCH(0,INDEX(COUNTIF($B$ 1:B1,$A$2:$A$7),0,0),0)),"")
To Sort A-Z
In C2 & copy down:
=IFERROR(INDEX($B$2:$B$4,MATCH(SMALL(COUNTIF($B$2: $B$4,"<="&$B$2:$B$4),ROWS($2:2)),COUNTIF($B$2:$B$4 ,"<="&$B$2:$B$4),0)),"") Array formula, CTRL + SHIFT + ENTER.

Or another way, might be!
Sort A-Z
In B2 & copy down:
=IFERROR(INDEX($A$2:$A$7,MATCH(SMALL(COUNTIF($A$2: $A$7,"<="&$A$2:$A$7),ROWS($2:2)),COUNTIF($A$2:$A$7 ,"<="&$A$2:$A$7),0)),"") Array formula, CTRL + SHIFT + ENTER.
Copy & Paste as values. On the Data Tab Remove Duplicates.

Kevin

Thanks for your response!