0

I have a Google Sheet of a Lesson Plan (which I hope is editable by all). There are five (or more) columns (F:J) representing available lessons and a list of potential students (rows A10:A) which may increase by adding more rows. Which student attends which lesson is indicated by an 'x' in the appropriate cell.

The aim is to produce a summary column (for application elsewhere) B10:B showing the days of the week that each student has lessons. For domestic reasons, no student will have lessons on more than two different days of the available days, and an error appears if this is the case (eg, B13). The logic is, for each student row, to get the list of lesson days from row 3 corresponding to each 'x'; filter them and extract the unique days; and produce the two-letter summary. I currently rely on the lessons being clustered in weekday order, but this could be generalised by some (custom!) additional sorting in the formulae.

Additional lessons can be added simply by filling in the details in another column header (rows 3-7) - the lesson number L05+ automatically increments by an Arrayformula in F9. Similarly, adding a new student will, hopefully, extend the Arrayformulae in B10 and C10 - the latter being the problem area (see below).

I have chosen to use helper columns for intermediate products in the process for clarity (though their source data could be passed directly to the formula in B10) - see the formula, eg, in C10. Firstly I filter the whole list of lesson days in F$3:$3 based on the 'x' values present in each row, eg, F10:10 - this gives a horizontal array with possibly many days in it. I then extract the unique day names from this list. Unfortunately, the UNIQUE function only seems to work on vertical arrays (why?), so I transpose the horizontal filter output first. Having extracted the unique days (which I could now sort if need be) I constrain the number returned to a maximum of three before transposing the short list back to a horizontal array which then appears in the helper columns C10:E10 as 0, 1, 2 or 3 entries - see various example sets of data. The IFERROR function nicely removes the error from totally blank rows. The Arrayformula in B10 successfully extracts the required summary from the helper columns.

Unfortunately, I have only been able to get the helper columns generated by using the formula (C10) repeated in each row and cannot see how to create an Arrayformula to achieve this, in spite of there only being one tiny part of the formula that is variable - the F10:10="x" part of the initial filter.

I would like to preserve the ability to extend the possible lesson list indefinitely by using the open-ended address forms (F$3:$3 and F10:10), but I could accept a fixed set of columns if necessary.

I appreciate that the second argument of the FILTER function essentially requires a horizontal array of logical values that change for each row according to the "x"s. This is automatically accomplished by the formula in C10 when it is copied down the column. However, for the array version, the logical values must change for each row, and there doesn't appear to be a simple substitution for the filter condition that applies to rows and columns, especially in a fully open-ended version, eg, F10:="x". The 'half-open' versions, F10:J or F10:F don't give the required output.

I have tried the trick of producing an array of varying logical values by an elaborated double IF statement, eg, IF( COLUMNS(F3:3), IF( F10:10="x", ROW(A10:A), 0) )=ROW(A10:A). This is illustrated on sheet Lessons(2) where I have expanded the helper columns and left out everything but the logical array kernel. Obviously, I have had to use a fixed length array here because of the limited number of helper columns whilst omitting the array constraint for clarity.

Unfortunately, if the FILTER function is then wrapped round this logic kernel, [=ArrayFormula( if ( A10:A="", "", filter(H$3:L$3, if( columns(H:L), if( H10:L="x", row(A10:A), 0 ) = row(A10:A) ) ) ) )], it produces the error: "FILTER range must be a single row or a single column". I cannot see why the logic kernel successfully fills the helper columns but is not regarded as a one-row array in the FILTER.

I have also tried using HLOOKUP to obtain the list of used lesson days with a view to feeding that directly to the UNIQUE/TRANSPOSE thus bypassing the FILTER function. HLOOKUP obviously needs the lookup row to be the first row (why, oh, why?), so I have experimented with creating a working two row array using {...;...} with the current "x" row first and the fixed row second. These would need to be single cell extracts so that HLOOKUP finds only one "x" each time. I could have done this using INDIRECT(ADDRESS(ROW(),COLUMN()) - if only INDIRECT worked with Arrayformula. Or INDEX, or OFFSET... Interestingly, ADDRESS does seem to work.

My difficulty seems to stem from trying to filter information (row 3) which is outside the normal Arrayformula 'data' area F10:end, and remains fixed. The sample sheet gives the desired outcome in terms of appearance, but I am trying to get an Arrayformula version of the repeated formula in C10.

I'm sure the problem could easily be resolved by using a script. However, although I have a fair amount of experience with VBA in Excel, I have a steep learning curve to face with Google. Similarly, QUERY may offer a solution, but I have no experience of queries at all. Given that the repeating formula is really quite straightforward, I find it frustrating not to be able to convert it to an Arrayformula. Maybe, one day, Google will get round to making more of the basic functions compatible with arrays?

Any help in converting the formula in C10 to a successful Arrayformula would be much appreciated. I am hoping that one of you out there has a few more tricks up your sleeves... If you do have a solution, it would be nice if you duplicated the main 'Lessons' sheet and placed the formula in C10 - if I can't fathom how it works, I'll just have to ask, if I may!

  • 3
    I agree with your sentiment that this is quite long. It certainly (more than) documents what you have tried so far, which is a great first step. I would recommend removing some of the backstory, since, while it's great that you are forthcoming with your limitations, people will already understand you are not an expert if you are coming to them for help. – jonsca Jan 22 '17 at 3:57
  • 2
    Parenthetically, you can also use backticks to cordon off some of the formulae that you have listed inline which might make those sections a bit more easy to read. – jonsca Jan 22 '17 at 3:59
  • Thank you @jonsca for your prompt response. In browsing for a solution, I noticed lots of other posts with very thin explanations which simply prompted responses that were not what was wanted and requests for more information! Perhaps I have overdone it. However, it is possible that the problem lies in some of the other functions, eg, UNIQUE, TRANSPOSE, etc, so I thought is prudent to leave them in place rather than strip the example down even further than I have already. I don't currently know what backticks are, but I padded the formulae with spaces for legibility - I'll look it up. – Nick C Jan 23 '17 at 16:36
  • Looks like you found the backticks. I tried to write them out in the comment, but I did not escape them properly ``` ``` – jonsca Jan 23 '17 at 23:58

Your Answer

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

Browse other questions tagged or ask your own question.