1

I have an Excel sheet with students and subjects listed in it as m:n relationship.

The number of subjects are fixed (6), but the number of students are growing.

So basically, each student will have 6 rows for him/her, corresponding to each subject.

Sometimes it so happens, that the data entry person missies adding one subject to a student.

A monthly report is pulled (from a legacy system) and we found this error/miss.

How to quickly add the missing subjects for each student in the excel sheet?

We tried counting the number of rows by each student, but the number of students whose subjects are missed (i.e. whose row count is < 6) are way too many to fix manually.

Any excel formula which can quickly and easily do it?

example:-

Student    Subject

Bruce      Science

Bruce      Arts

Bruce      Maths

John       Science

John       Maths

John       Social Studies

So as per the above example, I want a formula which will add 2 rows, one for John with the missing Arts subject, and one for Bruce with the missing Social Studies subject.

Improve this question
7
  • Could you put a couple of examples, a good one and a bad one?
    – jcbermu
    Oct 9, 2017 at 8:48
  • 1
    Why not have the subjects as columns and the students as rows?
    – Seth
    Oct 9, 2017 at 10:12
  • Because the legacy system gives us the data in rows only.
    – Abhijeet Mitra
    Oct 9, 2017 at 13:27
  • @jcbermu question updated with example. Thanks
    – Abhijeet Mitra
    Oct 9, 2017 at 13:33
  • First part of correcting it will be finding the places where it is wrong. Fore that a =COUNTIF(A:A, A2) should work, (it look in cell A2 and finds Bruce. Tnan it counts how often Bruse is in column A. The answer should be six. Expand that formula down and you have part one of X on the road to the solution.
    – Hennes
    Oct 9, 2017 at 13:55

1 Answer 1

Reset to default
0

Say we place the student names in column A and the subjects on the first row like:

enter image description here

In H2 enter:

=COUNTA(B2:G2)

and copy down. Then filter column H to find the missing items:

enter image description here

Improve this answer
2
  • Unfortunately this can't be done, as the legacy system provides the data in rows only, like:- Bruce Art Bruce Music John History John Art John Science So I want a formula, which will tell me that Bruce is missing History and science, or a formula which will add rows with Bruce with History and Bruce with Science
    – Abhijeet Mitra
    Oct 9, 2017 at 13:23
  • My own preference would be to transfer data to a MS Access (or other database) table [support.office.com/en-us/article/… and query for students where count of subject < 6 [support.office.com/en-us/article/…. Databases are designed to deal well with sets of data, while spreadsheets work at the cell level.
    – DrMoishe Pippik
    Oct 9, 2017 at 18:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .