2

I have two lists (A and B). I want to filter list "A" (a multi column list) with values in list "B" to create a Final List, list "C".

List "A" has multiple records for each student ID number. List "B" has a list of student ID numbers I want to include in list "C". I need all references to student ID Numbers in List "B" to appear in List "C" and I want all references to student ID numbers listed in list "A", but not in list "B" to be removed from List "C".

List "A" Structure

ID #    First   Last     Class
---     ---     ---      ---
123     Tim     Jones    Math
123     Tim     Jones    English
111     Amy     Smith    Science
111     Amy     Smith    GYM
222     Mike    Young    Math
222     Mike    Young    English

List "B" Structure

ID #    
---     
123     
222     

Desired List "C" Structure

ID #    First   Last     Class
---     ---     ---      ---
123     Tim     Jones    Math
123     Tim     Jones    English
222     Mike    Young    Math
222     Mike    Young    English

I'm working with this formula, but I'm not sure how to structure the the part after this, where Col1 = nor am I positive it will work.

=query(importrange("1vutSseBMdVaivTbpX32rbXF4-7PNYImcAbsv6Q9Y-cE","data!A2:F"), " Select * where Col1 ='"&A2&"' ",1)

I've also tried the below based on ttarchala answer, but it is only returning one row (ID#327288).

=filter(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1vutSseBMdVaivTbpX32rbXF4-7PNYImcAbsv6Q9Y-cE", "data!A2:2"), match(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1vutSseBMdVaivTbpX32rbXF4-7PNYImcAbsv6Q9Y-cE", "data!A2:2"), IMPORTRANGE("https://docs.google.com/spreadsheets/d/1vutSseBMdVaivTbpX32rbXF4-7PNYImcAbsv6Q9Y-cE", "myStudents!A2:A"), 0))

Any help is greatly appreciated. Thank you!

3

Use a FILTER/MATCH combination. Example on your data below:

=filter(C52:F57; match(C52:C57; I53:I54; 0))

enter image description here

  • Hey @ttarchala! Thanks for offering help. I'm working with your example and have written this formula, but it isn't working. Can you take a look and let me know if you can see what I'm missing? =filter(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1vutSseBMdVaivTbpX32rbXF4-7PNYImcAbsv6Q9Y-cE", "data!A2:2"), match(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1vutSseBMdVaivTbpX32rbXF4-7PNYImcAbsv6Q9Y-cE", "data!A2:2"), IMPORTRANGE("https://docs.google.com/spreadsheets/d/1vutSseBMdVaivTbpX32rbXF4-7PNYImcAbsv6Q9Y-cE", "myStudents!A2:A"), 0)) – Mr. B Feb 7 '18 at 18:46
  • 1
    What error are you getting? I can't test your formula without permission to access your spreadsheet. If all else fails, put the results of importranges in separate sheets locally and filter from there. – ttarchala Feb 8 '18 at 8:17
  • =filter(data!A2:F, match(data!A2:A, myStudents!A2:A, 0)) @ttarchala, this worked. Thanks for your help! – Mr. B Feb 8 '18 at 16:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.