I'm trying to merge two datasets in order to insert default rows for missing data. The use case is that I have a list of dates and attendance numbers for training sessions on those dates, but if I have no records at all for a training session then it's missing from the list.

In my sheet at the moment I have a two column set of dates and attendance numbers, and in another sheet, I have worked out all the Wednesdays and Fridays (training days) between the start and end dates of all the sessions we have data for.

Is there a way to merge the two datasets together so that the zero attendance for each session is the base set and then I merge in the rows for which I have data? I've tried using some of the query commands but if I specify two datasets using {Sheet1!A1:A,Sheet2!B1:B} I get array errors.

The attendance information is currently gathered with a query like this:

=QUERY({Records!A2:B}, "SELECT Col1, COUNT(Col2) WHERE (Col1 IS NOT NULL) 
 GROUP BY Col1 ORDER BY Col1 ASC LABEL Col1 'Session Date', COUNT(Col2) 'Skaters'") 

where the Records sheets is just date and names.

If I update it to read from two datasets

=QUERY({Records!A2:B, Scratch!B2:B}, "SELECT Col1, COUNT(Col2) WHERE (Col1 IS NOT NULL) 
 GROUP BY Col1 ORDER BY Col1 ASC LABEL Col1 'Session Date', COUNT(Col2) 'Skaters'")

then I get a #REF! error of Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 982. Actual: 999. Seems fair, as it's created a misaligned dataset, rather than merging based on the date column.

I'm probably treating the spreadsheet a bit too much like a database, and while I would be more comfortable dropping into the script editor to resolve this I'm trying to learn a few spreadsheet techniques.

  • What errors are you getting? – Rubén May 5 '18 at 17:30
  • Added some more info. – chooban May 6 '18 at 9:30
  • We could use FILTER, QUERY, OFFSET, ARRAY_CONSTRAIN and probably others functions to limit the number of rows returned by an open ended reference like Sheet1!A1:A. Have you tried any of this to avoid thee "mismatched row size" error? – Rubén Jun 24 '18 at 18:40

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.