I have a data set as follows
| Objective ID | Objective Name | Objective Start | Objective Finish | Key Result ID | Key Result Name | Key Result Start Date | Key Result Finish Date | Task Name | Task Finish | Inv Flag | Issue Name | Issue Resolution Date | Risk Name | Risk Resolution Date |
| OB02 | OBJ 02 | 13-06-2024 | 31-12-2026 | KR4 | Test 1 | 13-06-2024 | 13-06-2024 | Task 1 | 30-09-2024 | KD | (blank) | (blank) | (blank) | (blank) |
| OB02 | OBJ 02 | 13-06-2024 | 31-12-2026 | KR4 | Test 1 | 13-06-2024 | 13-06-2024 | Task 2 | 31-12-2026 | KD | (blank) | (blank) | RSK1 | 30-11-2025 |
| OB02 | OBJ 02 | 13-06-2024 | 31-12-2026 | KR4 | Test 1 | 13-06-2024 | 13-06-2024 | Task 3 | 31-12-2026 | KD | Iss01 | 30-06-2026 | RSK1 | 30-11-2025 |
| OB02 | OBJ 02 | 13-06-2024 | 31-12-2026 | KR4 | Test 1 | 13-06-2024 | 13-06-2024 | Task 5 | 30-09-2021 | KD | (blank) | (blank) | (blank) | (blank) |
| OB02 | OBJ 02 | 13-06-2024 | 31-12-2026 | KR5 | Test 2 | 13-06-2024 | 13-06-2024 | Task 1 | 31-12-2026 | KD | (blank) | (blank) | (blank) | (blank) |
| OB02 | OBJ 02 | 13-06-2024 | 31-12-2026 | KR6 | Test 3 | 13-06-2024 | 13-06-2024 | Task 2 | 01-01-2025 | KD | (blank) | (blank) | (blank) | (blank) |
| OB02 | OBJ 02 | 13-06-2024 | 31-12-2026 | KR7 | Test 4 | 13-06-2024 | 13-06-2024 | Task 1 | 31-12-2026 | KD | (blank) | (blank) | (blank) | (blank) |
| OB02 | OBJ 02 | 13-06-2024 | 31-12-2026 | KR8 | Test 5 | 07-04-2025 | 07-04-2025 | Task 10 | 30-11-2027 | KD | (blank) | (blank) | (blank) | (blank) |
| OB02 | OBJ 02 | 13-06-2024 | 31-12-2026 | KR9 | Test 6 | 07-04-2025 | 07-04-2025 | Task 1 | 31-12-2026 | KD | (blank) | (blank) | (blank) | (blank) |
| OB02 | OBJ 02 | 13-06-2024 | 31-12-2026 | KR9 | Test 6 | 07-04-2025 | 07-04-2025 | Task 2 | 31-12-2026 | KD | (blank) | (blank) | RSK2 | 30-11-2025 |
I want to create a data structure to set up a hierarchy in a pivot table based on parent child
OBJ - highest, then KR , then Task then risk issues but a unique hierrachy
What I want is something like this :
| Type | Name | Start Date | End Date |
| Flag | KD | - | |
| OBJ | OBJ 02 | 13-06-2024 | 31-12-2026 |
| KR | Test 1 | 13-06-2024 | 13-06-2024 |
| MS | Task 1 | - | 30-09-2024 |
| RI | Risk Name | - | |
| Is | Issue Name | - | |
| MS | Task 2 | | 31-12-2026 |
| RI | RSK1 | | 30-11-2025 |
| Is | ISS01 | | 30-06-2026 |
THe dates should be summarised as start and end with end dates only for risk and issues
If the KR is repeated for multiple tasks and tasks linked to risk and issues I should see the KR only once and the related task only once as above
