Excel - find a value and copy into different sheets

Question

I need some help with a formula i am trying to make for a mark book. I have a work book, with a sheet labelled "Master" that contains students unique reference numbers in col A2, their names B2/C2 and then the four subjects they study in col D2,E2,F2 and G2. I also then have separate sheets for each individual subject.

I would like to look up the subjects on the master sheet (in the four columns) and if "Maths" is a subject a student takes in one of those columns, i would like the corresponding student name and reference from that row to be copied and pasted into the sheet "Maths" so further information can be entered regarding that subject/student and class.

I have tried working with a IF and MATCH formula but it seems to get quite complex, as I have over 400 students who each have four subjects, and the subjects can appear in anyone of the four subject columns.

Any suggestions?

TIA!

Answer 1

There are two possible solutions, the easiest is:

In the Math sheet, insert this:

[cell B2]

=IF(Sheet1!D2="Math";Sheet1!B2;IF(Sheet1!E2="Math";Sheet1!B2;IF(Sheet1!F2="Math";Sheet1!B2;IF(Sheet1!G2="Math";Sheet1!B2;""))))

[cell C2]

=IF(Sheet1!D2="Math";Sheet1!C2;IF(Sheet1!E2="Math";Sheet1!C2;IF(Sheet1!F2="Math";Sheet1!C2;IF(Sheet1!G2="Math";Sheet1!B2;""))))

And drag them down trough the document ...

---

Here is a sample test I made:

Students sheet:

-------------
1001    Toni    Almeida     Math        Portuguese  Programming     Another
1002    David   P           Portuguese  French      Programming     Another
1003    Neil    C           Math        Portuguese  Programming     Another
1004    James   H           Portuguese  French      Programming     Another
-------------

Result (Math sheet):

-------------
1001    Toni    Almeida 
1002    
1003    Neil    C   
1004    
-------------

---

You can also create a query:

Note: This works at least in Google Spreadsheet, I don't have MS Office to test this in Excel.

=query(B2:G5;"select B,C where D='Math'OR E='Math' OR F='Math' OR G='Math'";1)

Result:

-------------
1001    Toni    Almeida 
1003    Neil    C   
-------------

Answer 2

The easiest solution would be to filter columns D2,E2,F2,G2 then copy/paste results to different worksheets. If you want this to be done autmatically, you'll need to write a macro. Regards,

edit: Move your data to a sheet called "Main" and alt+F11 to open visual basic editor, insert>modules, select module and paste this code below and save. alt+F8 to open macros and run ProcessList Macro, it will create your sheets automatically.

Sub ProcessList()
Dim course As String
Dim studentID As String
Dim studentName As String
Dim studentSurname As String

Application.DisplayAlerts = False
For Each w In Worksheets
    If Not w.Name = "Main" Then
        w.Delete
    End If
Next
Application.DisplayAlerts = True

i = 0
While Not Worksheets("Main").Cells(2 + i, 1) = ""
i = i + 1
Wend
studentcount = i

For i = 0 To studentcount - 1
    studentID = Worksheets("Main").Cells(2 + i, 1).Value
    studentName = Worksheets("Main").Cells(2 + i, 2).Value
    studentSurname = Worksheets("Main").Cells(2 + i, 3).Value
    For j = 0 To 3
        course = Worksheets("Main").Cells(2 + i, 4 + j).Value
        If Not course = "" Then
        Call checkcourse(course)
        Call insertStudentData(course, studentID, studentName, studentSurname)
        End If
    Next j
Next i

End Sub

Sub checkcourse(course)
found = False
For Each w In Worksheets
    If w.Name = course Then
        found = True
    End If
Next
If found = False Then
        Worksheets.Add().Name = course
End If

End Sub

Sub insertStudentData(wsName As String, studentID, studentName, studentSurname)
i = 0
While Not Worksheets(wsName).Cells(2 + i, 1) = ""
    i = i + 1
Wend
Worksheets(wsName).Cells(2 + i, 1).Value2 = studentID
Worksheets(wsName).Cells(2 + i, 2).Value2 = studentName
Worksheets(wsName).Cells(2 + i, 3).Value2 = studentSurname
End Sub