TransWikia.com

Google Sheets Filter Multi Column List With Another List Using A Function

Web Applications Asked on December 15, 2021

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!

2 Answers

@ttarchala provided a very helpful answer. If you need to invert the selection you can simply do it like this:

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

Answered by Markus Frömmel on December 15, 2021

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

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

enter image description here

Answered by ttarchala on December 15, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP