» Filtering a List into Unique Records Using the COUNTIF Formula
CATEGORY - Excel Filter
VERSION - All Microsoft Excel Versions
1. Select cell B2, and insert the formula =IF(COUNTIF($A$2:A2,A2)1,1,0).
2. Copy the formula from cell B2 downwards.
3. From the Data menu, select Filter, AutoFilter.
4. Select cell B1 and press Alt+Down Arrow to open the filtering dropdown list or open it by clicking the dropdown arrow and then selecting 0.
Book Store:
Recommended Books:
CountIF would not work
Gizmo
By following the instructions above, the "To filter a List into unique records using the COUNTIF formula:" would not work for me.
filter list using countif
Tarqwin
I had to alter the formula for it to work,
=IF(COUNTIF($A$2:$A$13,A2)=1,1,0)
where $A$2:$A$13 was the list,
I could have renamed it List1 and had this
=IF(COUNTIF(List1,A2)=1,1,0)
and again drag the formula down.
This formula does not work for UNIQUE records
jP_in_Calif
I make the assumption that this ExcelTip is an error on the part of the webmasters post.
Using this formula will only produce a value of '1' (in the cell of the formula) where there is only 1 instance of the element in th list. If there are multiple instances of the element in the list, the returned value is 0.
As stated in the example, the COUNTIF portion of the function will return the 'count' of the number of occurances of 'criteria' in 'list' i.e. A2 in range a2:a12.
The portion of the formula containing 'IF', (the equals 1,1,0) states that, if the result of COUNTIF is =1, then its 1 else it's 0 .
Formula Doesn't work and Screen Capture is Wrong
Christine
I couldn't get this formula to work, even with the changes suggested. Also, the screen capture doesn't appear to relate to this tip at all.
Formula Doesn't work and Screen Capture is Wrong
Tarqwin
That's right, the conditional format is to highlight the rows with the unique occurences in column A with this formula for all the cells in that row and then drag down.
=IF(COUNTIF(list1,$A2)=1,1,0) with formatting to suit
Filter for Unique and Multiple Occurences
Nick
Rather than give you only the first occurrence, this formula will search the entire column and show which data have unique entries, and which have multiple entries:
=IF(COUNTIF($A$2:$A$65536,A2)=1,"UNIQUE","MULTIPLE")
Then use Data-->Filter-->AutoFilter
Change in formula
Thangamani
This formula will be a better option.
=IF(COUNTIF($A:$A,A2)=1,"Unique","Multiple")
