Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 Quote:
Last edited by Kevin@Radstock : April 3rd 13 at 12:16 PM |
#4
|
|||
|
|||
Thanks for your response!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Web Farming | Excel Programming |