Hello everyone,
I need help in accomplishing the final product. See step by step information below:
Raw data:
Name
| Dept
| Dept % 1
| Dept % 2
| Role
|
|---|
| Maria | LT | 25% | 80% | Core |
| Maria | BT | 55% | 0% | Embed |
| John | LT | 75% | 25% | Embed |
| Lisa | CT | 0% | 100% | Core |
| Lisa | LT | 60% | 20% | Embed |
Step 1:
create column 5. See formula in column title for more information.
| 1 | 2 | 3 | 4 | 5 | 6 |
Name
| Dept
| Dept % 1 | Dept % 2
| =IF(Dept % 2 >=0.35, '>35%','<35%')
| Role
|
|---|
| Maria | LT | 25% | 80% | >35% | Core |
| Maria | BT | 55% | 0% | <35% | Embed |
| John | LT | 75% | 25% | <35% | Embed |
| Lisa | CT | 0% | 100% | >35% | Core |
| Lisa | LT | 60% | 20% | <35% | Embed |
Step 2:
If duplicate name, select the row that has max value = (Dept %1 + Dep % 2).
Step 3: FINAL:
After eliminating duplicates,
Count name by role
Final Product:
Dept
| =IF(Dept % 2 >=0.35, '>35%','<35%')
| Count of Name by role = Core
| Count of name by role = Embed
|
|---|
| BT | >35% | 1 |
|
| JT | >35% |
| 1 |
| LT | <35% | 1 |
|
| >35% | 1 |
|
