4 Ways to Add Excel Sequence Number by Group

Sometimes we have a data set of repeated numbers that we wish to categorize and assign a sequence number to. In this article, we will learn four approaches to add excel sequence number by group.


📁 Download Excel File

Download the given excel file for practice.


Learn to Add Sequence Number by Group in Excel with These 4 Approaches

Here we will use the following sample datasheet to group the data and give a sequence number by group.


Approach

1. Applying COUNTIF Function

In this first method, we will use the COUNTIF function to add sequence number by group.                   

⬇️⬇️ STEPS ⬇️⬇️               

  • First, select cell D6.
  • After that, enter the following formula.

=COUNTIF($C$6:C6,C6)

Using COUNTIF function to add excel sequence number by group.

  • Then press the Enter key.

  • And after that drag the Fill Handle icon from cell D6 to D11.

Utilizing COUNTIF function to assign excel sequence number by group.

Here we can see that depending on the number of goals scored by each player, it grouped the data and gave a sequence number to each group.

📕 Read More: 5 Ways to Fill Down to Last Row with Data in Excel


Approach

 2. Utilizing IF Function

In this method, we are going to use the IF function to add sequence number by group.

⬇️⬇️ STEPS ⬇️⬇️

  • First select cell D6.
  • Then enter the following formula.
=IF(C6<>C5,1,D5+1)

Using IF function to add excel sequence number by group

  • Press the Enter key.

Utilizing IF function to assign excel sequence number by group.

  • Now select cell D6 and drag the Fill Handle icon from D6 to D11.

And we got the sequence number by group. But in this method, if the dataset is irregular then we have to sort the data in ascending or descending order before applying this method.

📕 Read More: 3 Ways to Auto Number or Renumber After Filter in Excel


Approach

 3. Adding Fixed Sequence Number Using IF function

Now we can also add the sequence number in a different way by using the same IF function. In this method, we can give a fixed sequence number to each group. And all the members of one group will get the same number.

 ⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, we have to insert a new Row between the header and the data.

Using IF function to add fixed sequence number by group

  • Then we have to select cell no D7.
  • After that enter the following formula.

=IF(C7=C6,D6,D6+1)

  • Then press the Enter key.

  • Then again select cell D7 and drag the Fill handle to D12 and we will get the following result.

Utilizing the IF function to assign fixed excel sequence number.

We can observe the change now if we compare the result to the prior result. Instead of assigning distinct sequence numbers to group members, this method assigns a single sequence number to all group members. For example, when Mbappe and Messi both scored the same amount of goals, the method grouped them and assigned them the same sequence number.

📕 Read More: 4 Ways to Repeat Rows a Specified Number of Times in Excel


Approach

 4. Implementing RANK Function

In this method, we will use the RANK function to assign a sequence number to the group members in excel.

⬇️⬇️ STEPS ⬇️⬇️

  • Select cell D6.
  • Then enter the following formula.
=RANK(C6,$C$6:$C$11,0)

Using the RANK function to group data and add sequence number.

  • Then press the Enter key.

  • Now select cell D6 and drag down the Fill Handle icon from D6 to D11.

Here we can see that the RANK function assigns the same sequence number to the duplicate numbers but the difference between method 3 and method 4 is the presence of duplicate numbers affecting the subsequent numbers. For example, when Mbappe and Messi both scored the same number of goals, the RANK function assigned each of them the same sequence number, which is 1. Then, for Rashford, a sequence number of 3 was allocated. There is no player with a sequence number of 2.

📕 Read More: 6 Ways to Autofill Sequential Letters in Excel


Takeaways from This Article

📌  In this article we illustrated four ways to add sequence number by group in excel.

📌  In the first method we used the COUNTIF function and in the second method we used  IF function to assign excel sequence number by group

📌  In the third method, we have used IF function to assign fixed sequence number by group.

📌  Finally in the fourth method we used RANK function to rank the data.


Conclusion

Finally, we have come to the end of this article. In this article, we tried to provide every possible way to assign excel sequence number by group. As many methods have been demonstrated here, you may quickly choose which one is suitable for you. If you have any questions, please leave them in the comments. Finally, we propose that you visit Excelden to discover more about Excel.


Related Articles

(Visited 57 times, 1 visits today)
Sakibul Hasan Nahid

Sakibul Hasan Nahid

Hello I am Sakibul Hasan Nahid. I have completed my BSc. in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo