Hi Folks,
Can anyone help to resolve below requirement.
Here is Data.
| Area | Year | Designation | Category |
| US | 12 | HR | A |
| UK | 12 | HR | A |
| US | 12 | HOD | A |
| UK | 12 | HOD | A |
| AUS | 12 | HOD | A |
| US | 12 | VC | A |
| US | 12 | VP | A |
| UK | 12 | VP | A |
| AUS | 12 | VP | B |
1st Requirement:
A. Show only data when any data in designation has not shared with two data values of Category.
Example: VP shared A and B, so it has to be not displayed.
B. If Areas (US, UK and AUS) are existed in Designation an Category, then show as 1 else 0.
Below is required information report in Pivot table.
I have used IsNull function for first column Designation but unable to insert same on Category.
| Designation | Category | US | UK | AUS |
| HR | A | 1 | 1 | 0 |
| HOD | A | 1 | 1 | 1 |
| VC | A | 1 | 0 | 0 |
2nd Requirement:
Data which is missed in above or not satisfied condition, should display like below in other Pivot table. (When Designation filed shares two Categories)
| Area | Year | Designation | Category |
| US | 12 | VP | A |
| UK | 12 | VP | A |
| AUS | 12 | VP | B |
