Results 1 to 2 of 2
2003-01-23, 15:13 #1
- Join Date
- Nov 2002
- Toronto, Ontario, Canada
- Thanked 0 Times in 0 Posts
Flag record based on Row and Column Criteria (2000)
With each passing, fruitless minute, the problem I face drives me closer and closer to maddness.
Imagine a worksheet with Col. A listing employee ID numbers, Col. B listing the department the employee belongs to, Cols. C-N filled with employee specific information, and Col. O,P,Q listing the Title, Surname and First Name of the individual who has authority for the department. The 3 potential titles for column O, in hirearchal order, are "Vice-President", "Director" and "Manager". Most employees will have multiple (Row) entries due to the fact that each department will usually have the full set (Manager, Director and VP) of authority figures or at least Director and VP.
What I would like to do is identify (possible in Col. R) all rows where for a single employee the lowest authority figure is listed. In some cases a single employee will have multiple authority figures listed at the same level (ie. 2 managers), in these cases both authority figures of the lowest level will need to be flagged.
I have attached a sample with notations in an effort to make my problem clear.
Please let me know if you require any additional information.
2003-01-23, 15:37 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 Posts
Re: Flag record based on Row and Column Criteria (2000)
I have attached your workbook with some columns added to the right. Columns H to J are auxiliary columns; they could be hidden. Range M1:N4 is an auxiliary table which could also be hidden. Column K contains the formulas that display "flagged" where appropriate.
Column H extracts the first character of the title; column I looks up the corresponding ranking number (Manager = 3, Director = 2, VP = 1) in the auxiliary table.
Column J contains array formulas (confirmed with Ctrl+Shift+Enter instead of Enter) that compute the highest ranking number (corresponding to the lowest level) for the employee; column K compares this to the ranking number is column I; if they are equal, "flagging" is put in the cell.