How to Find First Occurrence of a Value in a Column in Excel (5 Ways)

The article provides some easy methods on how to find the first occurrence of a value in a column in Excel. Sometimes we need to identify duplicate items or data in our Excel sheet. To find them, we need to find the first occurrence of a value in a column. Here we are using a dataset which contains IDs and Names of some guys.

excel find first occurrence of a value in a column


Download Practice Workbook


5 Ways to Find First Occurrence of a Value in a Column in Excel

1. Using Excel COUNTIF Function to Find First Occurrence of a Value in a Column

Suppose we want to identify the first occurrences of the names in the dataset. If any name occurs twice or more in this dataset, we will mark them as 0s, otherwise, it will be marked as 1. We can do this using the COUNTIF function. Let’s see the process below.

Steps:

  • Make a new column to identify the occurrences and type the following formula in cell D5.
=(COUNTIF($C$5:$C5,$C5)=1)+0

Here, the COUNTIF function keeps returning TRUE until it finds the same name in the column C. We added a 0 (zero) to get the numerical value.

  • Hit ENTER and you will see the output in cell D5.

excel find first occurrence of a value in a column

  • Use the Fill Handle to AutoFill the lower cells and this operation will mark subsequent occurrences of the names as 0.

Thus you can easily identify the first occurrence of a value in a column.

Read More: How to Find Last Occurrence of a Value in a Column in Excel (5 Methods)


2. Applying COUNTIFS Function to Find First Occurrence of a Value in a Column

We can also find the first occurrence by this using the COUNTIFS function. Suppose we want to identify the first occurrences of the names in the dataset. If any name occurs twice or more in this dataset, we will mark them as 0s, otherwise, we mark them as 1. Let’s discuss the process below.

Steps:

  • Make a new column to identify the occurrences and type the following formula in cell D5.
=N(COUNTIFS(C$5:C5,C5)=1)

excel find first occurrence of a value in a column

Here, the COUNTIFS function keeps returning TRUE until it finds the same name in the column C. The N function converts TRUE or FALSE to 1 or 0 respectively.

  • Hit ENTER and you will see the output in cell D5.

  • Use the Fill Handle to AutoFill the lower cells and this operation will mark subsequent occurrences of the names as 0.

excel find first occurrence of a value in a column

Thus you can easily identify the first occurrence of a value in a column.

Read More: How to Find Value in Column in Excel (4 Methods)


3. Find First Occurrence of a Value in a Column by Utilizing Excel ISNUMBER and MATCH Functions 

Applying the ISNUMBER function along with the MATCH function can be useful to find the first occurrence of a value in a column. Suppose we want to identify the first occurrences of the names in the dataset. If any name occurs twice or more in this dataset, we will mark them as 0s, otherwise, we mark them as 1. Let’s see the procedure below.

Steps:

  • Make a new column to identify the occurrences and type the following formula in cell D5.
=1-ISNUMBER(MATCH(C5,C$4:C4,0))

Here, the MATCH function searches for the value in C5, looks up through the range C4:C4 and returns the position where it finds an exact match. The ISNUMBER function returns TRUE if it finds a numeric value in it, otherwise it returns FALSE even if it has an error in it.

  • Press the ENTER button and you will see the output in cell D5.

excel find first occurrence of a value in a column

  • Use the Fill Handle to AutoFill the lower cells and this operation will mark subsequent occurrences of the names as 0.

Thus you can easily identify the first occurrence of a value in a column.

Read More: How to Find Top 5 Values and Names in Excel (8 Useful Ways)


4. Finding First Occurrence of a Value by Using Combined Functions 

We can also find the first occurrence of a value or data in a column by combining IF, INDEX, FREQUENCY, MATCH and ROW functions. Suppose we want to identify the first occurrences of the IDs in the dataset. If any ID occurs twice or more in this dataset, we will mark them as 0s, otherwise, we mark them as 1. The formula will be a little bit messy. Let’s go through the description below.

Steps:

  • Make a new column to identify the occurrences and type the following formula in cell D5.
=IF(INDEX(FREQUENCY(IF($B$5:$B$13&"#"&$C$5:$C$13<>"",MATCH("~"&$B$5:$B$13&"#"&$C$5:$C$13,$B$5:$B$13&"#"&$C$5:$C$13,0)),ROW($B$5:$B$13)-ROW($B$5)+1),ROWS($B$5:B5))>0,1,0)

excel find first occurrence of a value in a column

Here, the IF function returns 1 (TRUE) when it meets the criteria, else it returns 0 (FALSE). The FREQUENCY function determines the number of times a value occurs within a given range of values.

Formula Breakdown

  • ROWS($B$5:B5) —-> Returns
    • Output: 1
  • ROW($B$5:$B$13) —-> Becomes
    • Output: {5;6;7;8;9;10;11;12;13}
  • ROW($B$5) —-> Turns into
    • Output: {5}
  • MATCH(“~”&$B$5:$B$13&”#”&$C$5:$C$13,$B$5:$B$13&”#”&$C$5:$C$13,0) —-> Becomes
    • Output: {1;2;3;4;5;1;7;2;9}
  • IF($B$5:$B$13&”#”&$C$5:$C$13<>””,MATCH(“~”&$B$5:$B$13&”#”&$C$5:$C$13,$B$5:$B$13&”#”&$C$5:$C$13,0)) —-> Turns into
  • IF($B$5:$B$13&”#”&$C$5:$C$13<>””,{1;2;3;4;5;1;7;2;9}) —-> leaves
    • Output: {1;2;3;4;5;1;7;2;9}
  • FREQUENCY(IF($B$5:$B$13&”#”&$C$5:$C$13<>””,MATCH(“~”&$B$5:$B$13&”#”&$C$5:$C$13,$B$5:$B$13&”#”&$C$5:$C$13,0)),ROW($B$5:$B$13)-ROW($B$5)+1) —-> Becomes
  • FREQUENCY(IF{1;2;3;4;5;1;7;2;9}),{5;6;7;8;9;10;11;12;13}-{5}+1) —-> Turns into
    • Output: {2;2;1;1;1;0;1;0;1;0}
  • INDEX(FREQUENCY(IF($B$5:$B$13&”#”&$C$5:$C$13<>””,MATCH(“~”&$B$5:$B$13&”#”&$C$5:$C$13,$B$5:$B$13&”#”&$C$5:$C$13,0)),ROW($B$5:$B$13)-ROW($B$5)+1) —-> Returns
  • INDEX({2;2;1;1;1;0;1;0;1;0})
    • Output: {2}
  • IF(INDEX(FREQUENCY(IF($B$5:$B$13&”#”&$C$5:$C$13<>””,MATCH(“~”&$B$5:$B$13&”#”&$C$5:$C$13,$B$5:$B$13&”#”&$C$5:$C$13,0)),ROW($B$5:$B$13)-ROW($B$5)+1),ROWS($B$5:B5))>0,1,0) —-> Simplifies to
  • IF({2}>0,1,0)
    • Output: 1

Finally, we get the output as 1 because the ID in cell B5 occurs for the first time.

  • Hit ENTER and you will see the output in cell D5.

  • Use the Fill Handle to AutoFill the lower cells and this operation will mark subsequent occurrences of the names as 0.

excel find first occurrence of a value in a column

Thus you can easily identify the first occurrence of a value in a column.

Read More: How to Find Value in Column Using VBA in Excel (4 Ways)


5. Using Filter Command to Sort First Occurrences of Values in a Column

Suppose you want to see the repeat times of the names in column D and hence you want to see the position of the first occurrences of these names. We can do this by applying the Filter command. Please go through the description below.

Steps:

  • Make a new column to identify the occurrences and type the following formula in cell D5.
=COUNTIF($C$5:C5,C5)

Here, the COUNTIF function returns the number of times a name occurs in column C.

  • Now, hit ENTER and you will see the output in cell D5.

excel find first occurrence of a value in a column

  • Use the Fill Handle to AutoFill the lower cells and this operation will mark subsequent occurrences of the names as 0.

Thus you can see the number of times a name occurs in column D.

  • To Filter the first occurrences, select the range B4:D13 and go to Home >> Sort & Filter >> Filter

excel find first occurrence of a value in a column

  • Click on the marked arrow in the Match header. Mark 1 and then click OK.

  • After that, you will see all the duplicate IDs removed by the filtering. Only the first occurrences of the IDs will appear.

excel find first occurrence of a value in a column

Thus you can find only the first occurrence and Filter them in a column.

Read More: How to Get Cell Value by Row and Column in Excel VBA


Practice Section

In the following figure, I give you the dataset that we used in this article so that you can practice these examples on your own.


Conclusion

In conclusion, the key focus of this article is to provide some easy approach to find the first occurrences of some given values in a column in Excel. We used pretty basic functions for this purpose. If you have any better methods or ideas or feedback, please leave them into the comment box. This will help me enrich my upcoming articles.


Related Articles

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo