Split data in half evenly by adding column with value: 1 or 2 based on specific cell value - excel

Question

I have a query I pulled from a database with student information. The table has the following columns: StudentID, CourseID, CourseGrade.

The CourseGrade column can contain any of the following values: "Satisfactory", "Above Average" , "Average", "Fair", "Poor" and lastly "Withdrawn".

There are 2 counsellors whom are to be assigned to these students for advising/tutoring.

Note: A student may be enrolled in only 1 course or multiple courses

Each counsellor needs to be assigned to the same number of students (the entire list of students must be split in half, unless there are an odd number of unique StudentIDs, in this case one counsellor would have one more student). Regardless of the number of Courses a student is enrolled in they should only be assigned 1 counsellor.

---

This is what I would like to accomplish:

I would like to add a fourth column to the table called CounsellorNbr and traverse through all the rows in the table and alternate between adding the value, '1' and '2' to each students record. Again, if the same StudentID appears on adjacent lines in multiple courses, both should obtain the same number in this new column.

An acceptable alternative to adding a column with a number 1 or 2, the StudentID column could be highlighted in one of two colors. If that is any easier.

The only programs available to me for use are Microsoft Excel and Access. Ultimately I would prefer to be able to run a macro to accomplish this task as it is something that needs to be done often by non-technical individuals however I am having trouble determining what type of function would be needed to traverse a list like this and add the values (or cell colors) as I have described above.

I would greatly appreciate any helpful pointers suggestions or places to start.

Answer 1

Set D1 to 1, set D2 to

=IF(A1=A2, D1, 3-D1)

and drag/fill down.  This says: if the StudentID on the previous row equals the StudentID on the current row, then this is a secondary row for this Student (i.e., this is part of a multi-row data cluster for a Student, and it is not the first line).  In that case, the CounsellorNbr for this row should be the same as the CounsellorNbr for the previous row (because both rows are for the same student).  Otherwise (A1≠A2), this is the first row for a student, and so we want to assign the other CounsellorNbr from the one in the previous row. There are various ways to do this, but the simplest is arithmetic: subtract from 3.  3−1=2, and 3−2=1.

If you have more than 2 counselors in the future (say, five), you can replace the 3-D1 in the above formula with the somewhat simple-minded

IF(D1=5, 1, D1+1)

which says, “if the preceding student got the last (5th) counselor, this one gets the first one, otherwise just go on to the next counselor,” or the somewhat more artistic

MOD(D1,5)+1

which says, “take the preceding counselor number, divide by 5, take the remainder, and add 1.”  This looks more complicated than it is:

• 1÷5 is 0 with a remainder of 1
• 2÷5 is 0 with a remainder of 2
• 3÷5 is 0 with a remainder of 3
• 4÷5 is 0 with a remainder of 4
• 5÷5 is 1 with a remainder of 0

so it works out to exactly the same thing as the other formula; add one unless we’re at the last one, in which case we loop back to the first.