< Go Back

Flag first duplicate in a list

If you want to flag first duplicate in a list with a mark and then want to flag subsequent duplicates with a different marker then you can do this by using COUNTIF function in IF statement as follows.

Formula

This easy formula will help in finding the first duplicate in your excel list:

=IF(COUNTIF(A:A,A1)>1,IF(COUNTIF(A&1:A1,A1)=1,"x","xx"),"")

Explanation

COUNTIF method or function can be used to identify the first duplicate in excel list.  Users can also identify subsequent duplicates in the list using different markers. The formula is based on the IF statement and is called a COUNTIF formula. The formula first checks whether the value in a the is duplicated or not. If the formula finds duplicates of a value in a list then it flags its first occurrence with a certain mark and marks its subsequent values with different marks as defined by you in IF statement.

Example

The formula used in C5 in the figure below is as follows:

=IF(COUNTIF($B$4:$B$11,B5)>1,IF(COUNTIF($B$5:B5,B5)=1,"x","xx"),"")

The same formula will be used from cell reference C5 to C13.

For finding a duplicate of B5 this formula will be used:

=IF(COUNTIF($B$4:$B$11,B5)>1

If the value is not duplicated, then the IF statement returns like “ ” (empty)

The major work is done by the second formula of COUNTIF. The formula is used as an expanding reference ($B$4:B4). The formula expands when it is copied from B4 to B5 and till B11. The range of the second formula is relative which helps it to change as it moves down.

The second formula is as follows:

IF(COUNTIF($B$5:B5,B5)=1,"x","xx")

The formula works only when the COUNTIF detects and returns with a value entered more than 1. The formula is applied to each of the rows; if the detected count is 1, it is marked with “x” because it is the first time we see that data. When it is not 1, it is obvious that it must be subsequent duplicate and is marked with “xx”.

Where “xx” are just random markers which can be replaced by any markers of your choice.

Figure 1. Flag first duplicate in a list

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar