Buzz
Utilizing the cycle-based sequential numbering in Excel method provides an additional way to organize lists and number them according to a specific rule. This technique is advanced for those managing employee or student lists and categorizing them based on given conditions.
This article involves the use of various functions, such as the VLOOKUP function, which greatly assists in automating cycle-based sequential numbering in Excel. Hence, a solid understanding of these basic functions in Excel is essential. Refer to the VLOOKUP function for a quick review before following the guide on cycle-based sequential numbering in Excel below.
Guidelines for numbering sequences cyclically in Excel
Suppose we have 2 lists, one is the exam room list with columns such as registration number, sub-number, student code, full name, class, and exam room. The second list includes the sequence number and exam room.
Note: These two lists are on separate Sheets.
Your task is:
- Assign secondary numbers to each candidate for each exam room, ensuring that each exam room has no more than 5 people.
- Allocate exam room numbers to each student, knowing that each exam room must correspond to 5 assigned secondary numbers.
Step 1: First, name both sheets as the student list and exam room instead of their default names, or you can choose names according to your preference.
Step 2: On the Exam Room sheet, select the data range, leaving only the column names empty. Then, go to Formulas > choose Defined Names > Select Define Name.
Here we'll label this data as V.
Step 3: Return to the student list sheet, input the secondary number for the first candidate as 1. Then, input the following formula for the second candidate: =IF(B3=5,1,B3+1).
With B3 corresponding to the second candidate in column B. Here, if the candidate's list is not equal to 5, it will be incremented by 1; otherwise, if >5, it will start again from 1.
Step 4: Then, copy this for all the columns below to see the results when numbering is repeated cyclically in Excel.
However, we have only completed half of the task, which is assigning secondary numbers to each candidate for each exam room, ensuring that each exam room has no more than 5 people.
Step 5: Next, in the exam room column, which is column F, we input the following formula: =IF(A3=5,'V',VLOOKUP($A$3,A,2)).
This is a conditional function with data lookup. If the conditions of the V data on the Exam Room sheet are satisfied, the corresponding result from Column B on the Exam Room sheet will be displayed.
Step 6: The result will be as shown below. Here, we just need to copy it for the columns below, and the cyclic numbering in Excel will be completed.
The final result when cyclically numbering sequences in Excel will look like the image below. Of course, the example above is only applied to a list of 10 people, but imagine if we cyclically number sequences in Excel for a list of up to 1000 people, then the high efficiency of this trick will be evident. If you have any questions or encounter any difficulties during the process, feel free to leave a message here. The Mytour management team will help you resolve any queries.
Through the above article, we also see that the IF and Vlookup functions have many practical applications. If you are more interested in the Vlookup function, you can refer to our advanced article with guidance on using combining the Vlookup function and Left function, one of the advanced tricks for accounting and other business tasks.
