Comparision of two fields and result in other field
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)
