Benjamin Yergler

Member since 4/26/2019

Achievements

To start earning badges, sign up to be a Product Expert in our Product Expert Program .

Activity

No recent activity

Post history

Reply to Return First Row where cell contains "YES"

Google Docs Editors•1/27/2022

Thanks so much! That's a much simpler formula than I was thinking was needed! I came up with:

=IFNA(Textjoin(", ",True,FILTER(ARRAYFORMULA(IF(G3:EP3 = "YES",G$1:EP$1,"")),Len(ARRAYFORMULA(IF(G3:EP3 = "YES",G$1:EP$1,"")))>1))), which seems to work also, but is much more cumbersome.

-Ben

Return First Row where cell contains "YES"

Google Docs Editors•1/27/2022

I'm trying to compile a list of teachers that have recommended students for certain classes. We have a massive spreadsheet of teachers and their recommendations. I'd like to have a column that compiles a list of teachers that have said "YES" for a given student. See the

sample sheet for an example and the desired result (yellow). Thanks in advance!

3 replies

2 Recommended answers

Reply to Find and return rows where multiple columns match

Google Docs Editors•4/8/2021

Adam,

Thank you so much! That looks complicated, but great! Seems to working well. If I wanted to add an additional column to filter by, I can just add another clause to the query [and (H = "&TEXTJOIN("or H="...)], right?

Ben

Reply to Find and return rows where multiple columns match

Google Docs Editors•4/7/2021

Adam,

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

Find and return rows where multiple columns match

Google Docs Editors•4/7/2021

I'm helping my school create a master schedule. Some classes are only taught in one period, so you can't have a student signed up for two of those classes in the same period. I need to be able to find and compile cases where the same ID number is in two different classes in the same period. I've tried creating a query function for this, but can't get it right. On the "Conflicts" tab of this

Sample Sheet, the desired outcome is shown - both rows where there is the same ID and period.

Thanks in Advance!

5 replies

1 Recommended answer

Reply to Create data table for stacked bar chart

Google Docs Editors•12/15/2020

Thanks so much Hyde! Can this not be done without the helper table? The real dataset I'm working with is several hundred rows of ID numbers. It looks like I'd have to have a formula in each cell.

If you wouldn't mind, can you provide a bit of layman's terms explanation as to how this is working? I looked up Flatten() (which I didn't know existed) and I get countif(), but how is the row()-row(A20) working in this case?

Thank you,
Ben

Create data table for stacked bar chart

Google Docs Editors•12/15/2020

I have a list of ID numbers each week of students that are failing. I want to create a stacked bar chart that shows the number of students by the number of weeks they are on the list. I've tried Filter(VLOOKUP()) formulas, but as you get into the 3rd and 4th weeks, it gets really complicated. Is there a better way to do this? I know query() can tell you how many total times a given ID shows up with group by Count(), but that doesn't give the same information. I want to know on a given week, how many of the students are in that list that week for the first time, second time, 3rd time, etc. Here's a sample dataset to play around with:

https://docs.google.com/spreadsheets/d/1ynn46C4yW9QNM6rbsRGiS7uPwnWKhtJRr8Ir-P9wQXs/edit?usp=sharing

I manually color coded the cells to help visualize what the final product should be counting. Thanks in advance!

1 upvote

3 replies

1 Recommended answer

Return all rows where a matching ID number is found

Google Docs Editors•12/5/2020

I have two datasets. One has a list of ID numbers with the number of Ds or Fs a student has (with filters and dropdown boxes built in), the other has the specific courses that student has a D or F in. I want to return all the rows (to see all the classes) that each student has when they show up on the first dataset.

Here's the Sheet.

I've tried query, filter, and vlookup and am able to return one of the classes from the D/F Report data, but can't get it to list all the classes for all the kids found on the EPS Data. I know I'm close, and need a little tweak, but just can't get it figured out.

Thanks in advance!

1 reply

Reply to Combine query and CountIf to count the number of times a cell has a blank below it

Google Docs Editors•10/11/2019

Thanks Jean-Pierre. That helps. Looking at the output of the ArrayFormula separately makes it easy to see. I do have a few more follow up questions. I've never understood the difference between "select A" and "select Col1" in query. Is it only necessary to use Col language when the dataset your are querying is the output of another formula? Is that the main difference?

I tried adding a "where Col2 = '09'" clause to the query but it doesn't work because the ArrayFormula is only spitting out one column. How do I add Column B or C to the output so that it used as a filter condition with the "where" clause? I tried expanding all the A3:A's to A3:D, but it returns an error.

Thanks for all your help with this!
Ben

Reply to Combine query and CountIf to count the number of times a cell has a blank below it

Google Docs Editors•10/11/2019

Good catch. It looks like it's good now. Can you explain roughly how that formula is working? I'm having a hard time following it.

For the other cells, like "Freshmen with Multiples" can I just add conditions to the query language, like "where B = '09'"?

Reply to Combine query and CountIf to count the number of times a cell has a blank below it

Google Docs Editors•10/11/2019

Thanks Jean-Pierre, I just realized I had it set to view only. It is now editable if you want to play around with it. Your formulas work, but they aren't adding up. I would think that the Singles and Multiples should add up to the total students.

Combine query and CountIf to count the number of times a cell has a blank below it

Google Docs Editors•10/11/2019

I have a large dataset of ID numbers and failing courses. Some ID numbers only have one entry (Failing one course) but others have multiple, so the ID is blank for subsequent rows until you get to the next ID number. This is necessary so ID numbers are not counted twice elsewhere in the sheet. See the image below. I want to count the number of students that only have one line for their ID ("Singles") and the number of students that have multiple failing courses ("Multiples"). I tried doing some sort of Countif(query(), [cell below is blank]) function, but to no avail. Any suggestions on how this could be done?

Thanks in Advance!
Ben

https://docs.google.com/spreadsheets/d/1NbSs1NG4V-WPfEef3jvbVpDypvo6gAWmAIsDooV5-Ws/edit?usp=sharing

5 upvotes

7 replies

1 Recommended answer

Query giving erroneous "empty output" and Count(Query()) is summing not counting the results

Google Docs Editors•10/7/2019

https://docs.google.com/spreadsheets/d/1NbSs1NG4V-WPfEef3jvbVpDypvo6gAWmAIsDooV5-Ws/edit#gid=1472012100

On the sheet above, there are two highlighted cells in the D/F 9-23-19 tab. These are giving me issues. in the one, it is yielding "232" which is the sum of the results of a query, rather than the count of the results (should be 132; I know because when you run a regular query, it yields 132 rows). The highlighted cell in Column I should return a "1" because I added an entry to the "2019 RR Sign Ins" tab so that it would. Whatever is wrong with that formula is wrong with the whole column and several others. By the way, can I use an arrayformula there to simplify everything?

Thanks in advance!
Ben

1 reply

Reply to Query giving "empty output" error when it shouldn't.

Google Docs Editors•4/29/2019

Thanks, Yogi. Why is N('Teacher Conflicts'!D2:H10+0) different from N('Teacher Conflicts'!D2:H10)? I added the +0 to the formula I originally had (from Lance) and it seems to work perfectly now. Just trying to understand what's happening under the hood.

Ben

Reply to Query giving "empty output" error when it shouldn't.

Google Docs Editors•4/29/2019

Lance, et. al.,

Something isn't right with the formula in F4 still. Could you take a quick look at it again? When the date is set to 4/3/2019, it should only be returning 1 or 2 available teachers. It looks like its filtering out the first column of the query but not other columns. Also, it looks like the formula you wrote is missing a double quote and ampersand (&") at the end of the query, but its not giving an error. Why does the last part of the query not have those characters?

Reply to Query giving "empty output" error when it shouldn't.

Google Docs Editors•4/27/2019

Lance,

Thanks again for all your (and Yogi and Matt) help. Ok, one more quick question: How do you trim spaces in an Importrange? I expect that some people filling out the form will accidentally add a space to the end of their name and that will screw up the query in the sheet. I tried Trim(Importrange(...)), but it trims it to return just the first cell of the range to be imported. In the spreadsheet we've been working on, Yergler, D. has a space at the end.

Ben

Reply to Query giving "empty output" error when it shouldn't.

Google Docs Editors•4/27/2019

Thanks Lance. That helps a lot. The matrix stuff is over my head, I think. I better stick to what I know, which isn't much. I never understood the Col1 vs. A distinction until now. Your explanation helps a lot. Thanks for you help on this. Our school's deans and secretaries do this by hand for about 150 teachers and all their conflicts. It takes a week or two. They'll be thrilled with this idea.

Ben

Reply to Query giving "empty output" error when it shouldn't.

Google Docs Editors•4/27/2019

Is there no "Best Answer" option anymore?

Reply to Query giving "empty output" error when it shouldn't.

Google Docs Editors•4/27/2019

Yogi, Lance, and Matt,

Thanks so much for working on this! I knew posting it would make the formula and process a lot cleaner. It looks like the converting to numbers idea works pretty well, as does the Filter idea. The MK tab is a good approach, too, but I don't think I need it that fancy. I'm doing this for a colleague in my school who I think would freak out seeing all the error messages.

Can you guys tell me the difference between Matt's filter method and Lance's numbers method? Is there a situation where one is better than the other (for instance if a form response leaves all the conflict columns blank)? Why does the query work better saying Col1 instead of A? Is that because now they are all numbers?

Thanks again, guys! Much appreciated!

Ben

Query giving "empty output" error when it shouldn't.

Google Docs Editors•4/26/2019

I set this sheet up to give me a list of teachers that are available to work on a given date, with all their conflicting dates on a separate sheet. The query is supposed to return the teachers that do not have a conflict on the date provided. It is giving me an empty output error when there is a teacher that doesn't conflict with the date provided. I'm sure its a simple fix but I can't find it. Here's the editable link:

https://docs.google.com/spreadsheets/d/1MB-Y9UDDyv-VRwK-JkYQL3jzJfa9c1N-99Pu5K0_WAw/edit#gid=1801852794

I did have it working fine, but when I change the "Teacher Conflicts" sheet to do an import range, everything stopped working. Thanks in advance!

Ben

2 upvotes

17 replies

5 Recommended answers