Apr 7, 2021
Find and return rows where multiple columns match
Thanks in Advance!
Details
Informational notification.
This question is locked and replying has been disabled.
Community content may not be verified or up-to-date. Learn more.
See if the updated formula works with your data now.
Adam
Original Poster Benjamin Yergler marked this as an answer
All Replies (5)
Hi Benjamin Yergler,
Thank you for the sample sheet. I have added a sheet/tab called Help to your file that has a possible solution for you. It is:
- =ArrayFormula({'Class Rosters'!A1:G1;FILTER('Class Rosters'!A:G,'Class Rosters'!A:A=QUERY(QUERY('Class Rosters'!A:G,"select A,count(A) where A is not null group by A,F,G",0),"select Col1 where Col2>1",0))})
This will first look for all of the entries that the same ID, Semester, and Period. Then it FILTERs out the the data for those numbers.
I hope this helps!
Adam
Diamond PE Docs volunteer
Silver PE Classroom volunteer
Cert. Educator 1 & 2
Cert. Trainer
(not a Google employee)
When you've received a response that answers your question, please observe these forum courtesies:
• If you had a Sheets problem, leave your demo sheet shared as View Only as part of this solution's archive,
• Click Recommend on the post that best addressed your question, and
• Post again soon!
Apr 7, 2021
Thanks so much for the quick reply. It doesn't seem to be looking for matching period numbers, only IDs. When I typed another entry into the Class Rosters with that same ID but a different period, the filter still caught it. Can the query count 2 columns (ID and Period) and look for where Col1>1 and Col2>1?
Another follow up question: Some classes can be offered in two periods, say 3rd and 7th. Could the Filter and query look for both of those?
Thanks again!
Ben
Ben
Apr 8, 2021
See if the updated formula works with your data now.
Adam
Original Poster Benjamin Yergler marked this as an answer