Monday, 21 January 2013

How to Get Unique Values From A Range of Values Using CountIf Function

How to Get Unique Values From A Range of Values
Suppose we have a list of values with duplicates in Column A as shown below.


Now if you want to extract the unique values in Column B , we have to use the below formula.

Formula:

{=INDEX($A$2:$A$50,MATCH(0,COUNTIF($B$1:B1,$A$2:$A$50),0))}

Explanation:

Here $A$2:$A$50 is the range from which we want to extract unique values
Here we are extracting the unique values to Column B So that we should add this formula in Cell B2 , and drag it down.
This is a array based formula so that we should press Ctrl+Shift+Enter to work this formula.
#N/A will shown in the cells if the distinct values are over in the Range.

Thanks

TAMATAM

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts