Forum Discussion

Jose_Gonzalez84's avatar
Jose_Gonzalez84
Copper Contributor
Oct 03, 2024

Extract or copy rows of data from one sheet to another based on multiple criteria.

Hello I am not sure which excel function would be best to resolve this. A little background I work in a school setting where students are taking multiple classes and the data given to me is one excel worksheet containing every student, class, and their progress in said classes.

I have a workbook with a "rawdata" sheet that contains multiple rows where the unique ID is the student name (I know this is not practical since names can be redundant), and the following columns show their progress in various classes. I have a second sheet "mystudents" which contains only the students under my purview whos data I want to see. I would like to pull all the rows of data from "rawdata" to a new sheet based on matching the students listed in "mystudents" sheet. 

I have tried using the advanced filter and selecting the "Copy to another location" button then setting the List Range: rawdata, Criteria range: mystudents but this is not working. Am I using the correct function to accomplish this? Any ideas? 

Here is an example of what the "rawdata" sheet looks like:

Here is an example of "mystudents" sheet:

 

So Ideally it would pull every Subject, Grade, and Progress for each one of my students John Doe and Yesenia Gutierrez. 

 

This is what I have attempted:

 

Am I missing something?

 

excel
Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Oct 03, 2024

    Hi Jose_Gonzalez84 

     

    You did not mentioned so assumed you run Excel >/= 2021... With RawData and MyStudents ranges formatted as Tables, in sheet MyStudents:

    =IF( COUNTA( MyStudents[Name] ),
  FILTER(
    RawData,
    IFNA( XMATCH( RawData[Name], MyStudents[Name] ), 0 )
  ),
  ""
)

     

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Oct 03, 2024

    Jose_Gonzalez84 

    An alternative could be Power Query which is available in legacy Excel e.g. Excel 2013. In the attached file you can add data to the blue dynamic tables in sheets "rawdata" and "mystudents". Then you can click in any cell of the green table in sheet "mystudentsprogress" and right-click with the mouse and select refresh to update the green result table.

Resources