In my previous post I mentioned you should, as far as possible, keep data together in a single table rather than splitting it up between sheets. If you want to split it up for distribution purposes here is an easy way to do it.
Power Query can automate the process of segmenting your list. We will use the same list from the previous post. We want to segment by state. You can download the before and after files using the buttons at the bottom of this post.
The table below has three states combined.
Click the table (it is a formatted table) and click the Data ribbon tab and click the From Table/Range icon. This opens the Power window (this window is separate to Excel).
On the right of screen change the name to qWA and delete the Changed Type step using the cross on the left of the step name – see images below.
Click the icon on the left of the Date column header and select Date.
Click the filter drop down in the Supply_State column. You may need to click the Load more option see image below.
Change the filter WA and click OK – as per image below.
Check the Formula Bar for the formula created.
If the Formula Bar is not visible click the View ribbon tab and tick the Formula Bar tick box – see image below.
Note the WA in the Formula Bar. We will come back to that later.
Click the Close & Load icon on the Home ribbon tab. This will create a new sheet and table with just WA entries.
To create the other two state segments for NSW and VIC you could repeat the above steps.
Or you can copy the qWA query and amend it for the other states.
Right click the qWA query on the right of screen and choose Duplicate. Then change the query name and change the state in the Formula Bar in the last step and click Close & Load.
If the main table is updated you only need to click on the Data ribbon tab and click the Refresh All icon and the three state tables will be updated with the latest data.
Download the files.
Please note: I reserve the right to delete comments that are offensive or off-topic.