Go Back

Flag first duplicate in a list

Figure 1. of Flag First Duplicate in a List Excel Function.

Let’s say we are required to mark/flag the first duplicate in a list of items, we can use the COUNTIF function to achieve this. In this tutorial, we will step through the process of flagging duplicate values in a list through by nesting COUNTIF and IF functions.

Generic Formula


How to use the COUNTIF Function in Excel.

Essentially, the Excel COUNTIF Function is an operation syntax enclosed by an IF statement.

We are going to illustrate its use in the following three simple steps;

  1. Collect arrange data items in our worksheet.

In this example, we are going to be working with three lists of items in a grocery store. These are the items we need to check for duplicates. See illustration below;

Figure 2. of Items to be Checked for Duplicates.

  1. In cell F2 insert this formula =IF(COUNTIF($A$2:$C$8,A2)>1,IF(COUNTIF($A$2:A2,A2)=1,"x","xx"),"")

This will check all of the items in columns A,B and C for any duplicated items and flag the amount of duplicates (x, xx, xxx, etc) in column E of our worksheet.

See example illustrated below;

Figure 3. of Flag First Duplicate in a List Excel.

  1. Modify and enter the formula syntax in the example illustrated above, into the formula bar for the other cells in column F to get the desired results.

Figure 4. of Flag First Duplicate in a List Excel.


The initial segment of our formula syntax consists of an expansion reference ($A$2:C8) which is expanding while the formula syntax is modified and entered into cells down our list columns. (The initial A2 in our cell range is locked (absolute), the next one can be altered, so it is relative  as the operation syntax is inputted down our listed items.

Figure 5. of Final Result.

Instant Connection to an Expert through our Excelchat Service

Our live Excelchat Service is here for you. We have Excel Experts available 24/7 to answer any Excel questions you may have. Guaranteed connection within 30 seconds and a customized solution for you within 20 minutes

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

how to flag a cell that does not contain a list of certain values
Solved by B. D. in 16 mins
I need help finding duplicate values in a list.
Solved by Z. J. in 28 mins
I need a formula to see if the duplicate records (same email address) in my excel spreadsheet have the same first name. I have a list of records with the same email address but different first name. I need to identify which pairs have the same email but different first name.
Solved by M. C. in 14 mins

Leave a Comment