Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Excel Tip: Returning multiple rows

by
12th Jan 2015
Save content
Have you found this content useful? Use the button above to save it to your profile.

In a recent Any Answers question, Rikos asked for help with the following requirement: "I want to return multiple rows of data from one list based on a criteria from a different list. 

"For instance I want to put in a delivery note  number and return all the parts that were shipped on that delivery note number from a table that contains all the details." 

The rows needed to be extracted to appear on a despatch note template.

Various possible solutions were proposed including the use of a PivotTable, using an Advanced Filter, using array formulae or multiple IF() statements. It was also suggested that the task was more suited to a database than to a spreadsheet.

Power Query

Following on from our series about using the latest BI features in Excel, another possible solution would be to use the Power Query add-in or just one Excel 2013 Table based on another Excel Table.

We’ll use the Northwind invoices data for our example. To start with, the source list needs to be an Excel Table, or to be capable of being turned into an Excel Table. We can then use the From Table command in the Excel Table group of the Power Query to access our source list in Power Query.

We only want to include a few of our source columns in our despatch note. We can use the Choose Columns command to select the columns we want to include. Or we could select the columns we want to keep and use the Remove Other Columns command from the Remove Columns drop-down in the Manage columns group of the Power Query Home ribbon:

We can then ‘Close & Load’ our query to a new sheet. Here we have inserted some rows above our Table and a column to the left. We could set up our despatch note header in the inserted rows:

Table Slicers

So far, we have used Power Query to extract entire columns from our original list of transactions. We need to be able to apply a criterion to restrict the number of rows.

We will base our criterion on the OrderID field. In Excel 2013 we can do this by adding a Slicer to our Table. Slicers were introduced in Excel 2010 as a more graphical way of applying filters to PivotTables, they also enabled the connection of multiple PivotTables and PivotCharts to a single Slicer. This enabled Slicers to be used to make Excel dashboards interactive. In Excel 2013, Slicers were extended to work with Tables directly, although a Slicer can only be linked to a single Table.

Here, we have clicked in our Table that forms part of our despatch note and chosen Slicer from the Filters group of the Insert ribbon. We have then chosen to create a single Slicer based on the OrderID field:

Slicers have their own Options ribbon tab and we can use the Columns setting to increase the number of columns displayed in our Slicer to accommodate more OrderIDs.

We can then click on an individual OrderID in the Slicer to filter our Table to just show transactions with that OrderID. We could add a total to our Table, but to show what happens when the Slicer changes the number of rows, we will add the total to a normal cell beneath our Table. Here is our despatch note with a single transaction:

If we choose an Order with multiple lines, we see that our total is preserved and moved down accordingly:

Refresh

It’s worth pointing out that, if we add rows to our source Table, or edit existing entries, we could need to refresh our despatch note Table. We can either do this manually by right-clicking in the Table and choosing refresh, or we can set our query to refresh automatically.

The Connections command in the Connections group of the Data ribbon will display the connections in the workbook, including the Power Query connection. We can select it and click the Properties button and then set our query to be refreshed at an interval of a chosen number of minutes:

Note that whenever the Table is refreshed, whether automatically or manually, the Slicer will be reset to show all transactions.

Tables based on Tables

In fact, it’s also possible to do this without the use of Power Query. In Excel 2013 the Data ribbon, Get External Data, Existing Connections dialog includes a Table tab that allows you to use any Table in the workbook as the basis for a new Table, PivotTable or PivotChart:

This will include all of the columns in the source Table, so you would need to manually delete columns you don’t need. Having done this, you could add a Slicer in exactly the same way as for the Power Query example above.

Tags:

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.