Complex Custom Sort + Unique + Multiple Columns Formula

[Pher0ah]

Hi All,

Here is what I am trying to do; I seem to have failed putting this all together although the individual components seem to work fine...

Input: Multi-column table with 2 particular columns that have "Display Name" and "Title" respectively (not in that order and not adjacent either)

NOTE: I can't change the order or the input table as it comes from a database output.

Output: Table with three columns

1. Column A: Display Name (Unique set of names - i.e. everyone who appears in the original table but listed only once)
2. Column B: Title (relevant to the display name in column A) and here is the tricky bit; the titles need to be ordered (and thus the whole table) in a specific hierarchical custom sort (Director, Manager, Consultant, Staff, etc.)
3. Column C: Total number of hours worked (simple sumif from the same input table)

Now my trials have yielded a few things which work (please refrain from using LAMBADA functions at this stage, as it is still not in production) but I can't seem to put them together...

TBL_Input is a named field = OFFSET(INPUT!$A$1,1,0,COUNTA(INPUT!$A:$A)-1,COUNTA(INPUT!$1:$1))
COL_DisplayName is a named field = MATCH("Display Name",INPUT!$1:$1,0) #This provides me with the column number that has the DisplayNames
COL_Title is a named field = MATCH("Title",INPUT!$1:$1,0) #This provides me with the column number that has the Titles
DisplayNames is a named field = INDEX(TBL_Input,,COL_DisplayName)
Titles is a named field =INDEX(Tbl_Input,,COL_Title)
Title_Order is a named field ={"Director","Manager","Consultant","Staff"}

My Trial so far is this:
#This gives me the whole input table sorted by Title and then Name which is perfect; but then I can't narrow it down to the two columns and the unique names
=SORTBY(TBL_Input,MATCH(INDEX((TBL_Input,,COL_Title),Title_Order,0),1,INDEX((TBL_Input,,COL_DisplayName),1)

 

[Fluff]

How about

=LET(Name,INDEX(TBL_Input,,COL_DisplayName),Title,INDEX(TBL_Input,,COL_Title),u,UNIQUE(CHOOSE({1,2},a,b)),SORTBY(u,MATCH(INDEX(u,,1),{"Director","Manager","Consultant","Staff"},0)))