# Numbers: Combine several tables into one

I have a Numbers spreadsheet with a table for each room in our house listing jobs that need doing and a priority rating for each. For example:

``````Kitchen
Job                  Priority
Fix tap              1
Replace fridge       2

Bathroom
Job                  Priority
Replace cracked tile 3
Reseal bath          1
``````

I'd like to create a new summery sheet which pulls all the information into a new series of tables based on their priority. For example:

``````Urgent
Job                  Priority
Fix tap              1
Reseal bath          1

Important
Job                  Priority
Replace fridge       2

Low Priority
Job                  Priority
Replace cracked tile 3
``````

Is there a way to filter multiple tables like this?

Edit 7:10pm: I've discovered UNION.RANGES, which looks promising, but it only ever outputs the first cell of the unionised range. Is there some way to get it to fill the whole array into a new table?

Edit 11th December: Trying to use INDEX as follows to retrieve other cells from the array results in the error 'The formula contains a number outside the valid range.'

``````INDEX(UNION.RANGES(TRUE,Hall and Stairs::Fault:Estimated Cost,Dining Area::Fault:Estimated Cost,Utility::Fault:Estimated Cost,Lounge::Fault:Estimated Cost,Kitchen::Fault:Estimated Cost,Downstairs Loo::Fault:Estimated Cost),2,1,1)
``````
• Not sure if this will be enough to create what you are ultimately aiming for but you can place row and column numbers into, for example, the first column and row of the summary table, respectively, and use the INDEX function on UNION.RANGES to get all elements one by one to a different cell in the summary table. See the examples with INDEX in the “UNION.RANGES” Apple support web page. Commented Dec 10, 2022 at 20:04
• @Alper Thanks, but that seems to give an error, I've updated the question. Commented Dec 11, 2022 at 10:59
• UNION.RANGES is a bit quirky. If it gets 0 or FALSE as the compact mode argument, it expects all of the ranges it is given to have the same number of columns, and if 1 or TRUE, all to have the same number of rows. Otherwise, it combines everything into one long row array. So, I reckon at least one of the ranges in your December 11 example formula has a different number of rows than the rest. Commented Dec 11, 2022 at 12:54
• @Alper I get the same error whether I give the compact mode as true or false, and all the tables very definitely have the same number of columns. Commented Dec 11, 2022 at 21:49
• It looks like all of the ranges in your December 11 example formula are single columns from separate tables. UNION.RANGES might be combining all those single columns into a single row array. I suggest, passing `1, 2, 1` as the last three arguments to INDEX rather than `2, 1, 1`. If that works and your data is to remain structured in single columns, then you can pull in the rest by passing a different column index number to INDEX at each separate cell, i.e. `1, 3, 1`, `1, 4, 1`, etc, and create what you need. If this is not clear enough, I can provide the screenshot of an example. Commented Dec 12, 2022 at 2:10