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!
@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
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP