Hi,
I'm trying to sort a list which has been exported from a data base and then covered to a table in excel in to a hierarchical list with the most senior employee at the top of the list (CEO), then their next direct report, followed by subsequent employee reports. My data has circa 2300 rows with each employee on a new row and the relationship between a manager and employee is dictated by a manager number and a person number. The outcome is shown below in simple form but due to the number of rows its very time consuming to sort line by line.
| Hierarchy | Employee Number | Employee Name | Manager name | Manager Number | Job title |
| ++ | 123 | James Smith | CEO | ||
| ++/++ | 432 | Shaun Parker | James Smith | 123 | Chief People officer |
| ++/++/++ | 234534 | Georgina Fredricks | Shaun Parker | 432 | HR Manager |
| ++/++/++/++ | 542323 | Bob Smart | Georgina Fredricks | 234534 | Reward Manager |
| ++/++/++/++ | 13 | Charles Banker | Georgina Fredricks | 234534 | Payroll Manager |
| ++/++ | 2353 | Neil Luckins | James Smith | 123 | Chief Operations officer |
| ++/++ | 98 | David Dimbleby | Neil Luckins | 2353 | Operations Manager |
| ++/++/++ | 24 | Fred Smith | David Dimbleby | 98 | IT Manager |
Can someone post a detailed way of creating this?
Many thanks
Tony
Hi Tony,
One way is to create a lookup table that ranks the hierarchy codes into numeric equivalents. See file attached.
Note: if you sort based on the hierarchy codes in column A it will sort correctly anyway, but if you have new codes that don't follow this same pattern, then you may need to use the lookup table solution.
Hope that helps.
Mynda
Hi Mynda,
That makes perfect sense, however I don't have the hirarchy column to create the custom sort, all I have is columns B to F - I'd also like to create column A in the spreadsheet.
Thanks
Tony
In that case you'd use column F in your lookup table and then rank these job titles numerically and, or with the hierarchy code you displayed in column A.
