Sakibul Hasan Nahid
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.
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)
- Then press the Enter key.
- And after that drag the Fill Handle icon from cell D6 to D11.
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
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.
- Press the Enter key.
- 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
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.
- 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.
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
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.
- 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
- 6 Ways to Fill Down to Last Row with Data Not Blank in Excel
- 7 Simple Ways to Increment Month by 1 in Excel
