Extract from table based on criteria, then order results by other criteria

Hi All,

Sorry for the confusing header name but it's tricky to explain what I'm trying to do with just one line

Here goes the detailed way of explaining it...

Let's say I have a list of staff, the days that they work, and the area they work in. I want an extract of that into tables for each area. eg: one for all the Admin people, another for the Backoffice people, etc. Easy so far.

Each of those tables has a column for each day of the week and a list of all the names that are working that day. So in the Admin table there is a column for Monday with the names of the people that work that day. Ok, little trickier now.

On top of that, I want to be able to order those names to put people with priority to the top of the list. So in the Admin table there is a column for Monday with the names of the people that work that day, and the person tagged as Priority 1 is at the top of the list. Now we're getting complicated!

It took me a while but I managed to get the output I want however the formulas are very messy and inelegant. In one cell I have 14 IF statements, 14 CONCATENATEs, and 10 COUNTIFs. Ugh!

I've attached a sample spreadsheet to this post, with two tabs
- The first tab is a summary of what I'm trying to achieve, some sample data, and a sample output
- The second tab is my inelegant (but working) solution

Appreciate any tips, tricks or suggestions for making this a little more streamlined. Perhaps there are some formulas out there I'm not familiar with, or some way to cut this up for some Pivot tables? I'm open to idea!

Thanks AliGW - both for the welcome and uber-swift response

Unfortunately I'm still using using 2013, so I'm probably going to have to find an old-school solution.

You do have me curious though... what would be the 365 solution? Better table/filter tools?

That's a nice solution, John. A much simpler function though, as you said, it relies on the data being sorted first. My friend isn't exactly Excel savvy and I fear even sorting may be tricky for her but I could probably macro it or something to make it a button press.

Really appreciate the time you spent on this and your help. Rep added!

Thank you for the rep.

I did think of adding sort macro which could be invoked automatically when the workbook is opened but probably needs a button for changes in data.