CODEWITHSUNDEEP
google-sheetsgoogle-query-language
Jay Gale
Jay Gale

Reputation: 25

google sheets - How to combine multiple mini-tables into one table

Q: How do I get all the data from each of the mini tables into one list?

I have a staff rota on a spreadsheet, 35 "mini-tables" arranged in 5(weeks) rows of 7(days). I'm not allowed to change how the tables look. The columns are the same in each one, but the rows and data are variable. I gave them all a named range, basically numbered them from "one" to "thirtyfive".

In another tab I want to be able to combine the data from all of them into one list, ignoring blank rows, so I can query it. Ultimately I want a tab for each item of a list that is used for data validation in one of the cols of each table, but I would be okay with just filtering a sorted list of all the data. Hope that makes sense ... here is a link to the sheet. I have added a little dummy data, and you can see what I was trying and failing to do in the third tab

https://docs.google.com/spreadsheets/d/1GxTWWB2cEAXh-DG8pHY7sOMPn3-a1CIkvkxzxRq4xAI/edit?usp=sharing

Upvotes: 0

Views: 1161

Answers (1)

kirkg13
kirkg13

Reputation: 3010

I found your sample data quite confusing to work with, but I think I have an answer for you. Try the following formula, in A1 of Sheet6 of your sample sheet. Does it give you the merged list of data, as you want it?

=SORT( 
  QUERY({one;two;three;four;five;six;seven;eight;nine;ten;eleven;twelve;thirteen;fourteen;
         fifteen;sixteen;seventeen;eighteen;nineteen;twenty;twentyone;twentytwo;twentythree;
         twentyfour;twentyfive;twentysix;twentyseven;twentyeight;twentynine;thirty;thirtyone;
         thirtytwo;thirtythree;thirtyfour;thirtyfive},       
    "SELECT Col1,Col2,Col3,Col4,Col5,Col6,Col7 where Col8  CONTAINS '"&K1&"'", 1) , 1, TRUE)

enter image description here

Upvotes: 1

Related Questions