How to filter unique values in a column using exce

问题:

I'm in need of a solution for my excel workbook. I have a column with duplicate values in sheet1 and want to display the unique values of that column in sheet2. May be it looks simple but whenever there is a change in values in sheet1 those values should reflect in sheet2. I need an excel formula but not the VBA.

I tried few different options like array and stuff but could not able to get the proper solution. Appreciate your timely help. Many Thanks.

回答1:

Say the Sheet1 table is from A1 thru A100

In Sheet2 cell A1 enter:

=Sheet1!A1

In Sheet2 cell A2 enter the array formula:

=IFERROR(INDEX(Sheet1!$A$1:$A$100,INT(SMALL(IF(COUNTIF(A$1:A1,Sheet1!$A$1:$A$100)=0,ROW(Sheet1!$A$1:$A$100)+(COLUMN(Sheet1!$A$1:$A$100)*0.01)),1)),100*MOD(SMALL(IF(COUNTIF(A$1:A1,Sheet1!$A$1:$A$100)=0,ROW(Sheet1!$A$1:$A$100)+(COLUMN(Sheet1!$A$1:$A$100)*0.01)),1),1)),"")

and copy down.

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

Adjust the $A$1:$A$100 to match the exact size in Sheet1.

If you can apply a "helper" column in Sheet1, the formula can be tremendously simplified.