list distinct values in excel

问题:

This question is related to this question.

I have data as below.

 Col A
22-Oct-12
22-Oct-12
22-Oct-12
23-Oct-12
23-Oct-12
23-Oct-12
23-Oct-12
23-Oct-12
23-Oct-12
24-Oct-12
24-Oct-12
24-Oct-12

What I want is find the unique values in column B so that output would be as below.

 Col A
22-Oct-12
23-Oct-12
24-Oct-12

I tried with =IF(COUNTIF(A$1:A1,A1)=1,A1,"") but the problem is that I get 22-Oct-12 infront of first occurrence of 22-Oct-12, 23-Oct-12 infront of first occurrence of 23-Oct-12. Output is shown as below.

 Col A       Col B
22-Oct-12   22-Oct-12
22-Oct-12   
22-Oct-12   
23-Oct-12   23-Oct-12
23-Oct-12   
23-Oct-12   
23-Oct-12   
23-Oct-12   
23-Oct-12   
24-Oct-12   24-Oct-12
24-Oct-12   
24-Oct-12   

I don't want output like this. What I want is as below.

 Col A       Col B
22-Oct-12   22-Oct-12
22-Oct-12   23-Oct-12
22-Oct-12   24-Oct-12
23-Oct-12   
23-Oct-12   
23-Oct-12   
23-Oct-12   
23-Oct-12   
23-Oct-12   
24-Oct-12   
24-Oct-12   
24-Oct-12   

OR

 Col A      Col B          Col C
22-Oct-12   22-Oct-12   22-Oct-12
22-Oct-12               23-Oct-12
22-Oct-12               24-Oct-12
23-Oct-12   23-Oct-12
23-Oct-12   
23-Oct-12   
23-Oct-12   
23-Oct-12   
23-Oct-12   
24-Oct-12   24-Oct-12
24-Oct-12   
24-Oct-12   

Note : I can do this by MACRO, however I don't want to use MACRO. I want to get this done by use of excel functions.

Also, I could do above as per this answer, however I don't want to get done manually.

Everything by pre-defined excel function. NO MACRO or NO MANUAL WORK.

回答1:

Assuming numeric data (e.g. numbers or dates).....

In B1 use this formula

=MIN(A1:A1000)

and then in B2 (assuming Excel 2007 or later)

=IFERROR(SMALL(IF(A$1:A$1000>B1,A$1:A$1000),1),"")

...or in earlier Excel versions

=IF(COUNTIF(A$1:A$1000,">"&B1),MIN(IF(A$1:A$1000>B1,A$1:A$1000)),"")

confirm with CTRL+SHIFT+ENTER and copy down

If you have more than 1000 rows of data adjust accordingly.

This method will list dates in chronological order without repeats however they are ordered within the data

回答2:

Try this, and use CONTROL + SHIFT + ENTER

=IFERROR(INDEX($A$2:$A$20;MATCH(0;COUNTIF($B$1:B1;$A$2:$A$20);0));"")