Hi everyone,
I have a data which I get from the forms filled on a website. Few of the fields are marked as mandatory, but still are not filled for few forms. I have a list of columns which are mandatory, and which are not.
The reporting requirement here is:
1)to show how many of the mandatory/optional fields are filled and how many are left empty in each column?
2) No. of filled & unfilled fields per each ID divided by mandatory or optional.
Attaching the sample data and expected outcome.
Any help is appreciated. Thank you
Input Data:
| ID | Name | Gender | DOB | Email | Phone | State | Country |
| 1 | Jake | F | 8 | ab@gmail.com | 9832133213 | TX | US |
| 2 | John | M | 9 | bc@gmail.com | 9832133214 | TX | US |
| 3 | Milo | M | 10 | cb@gmail.com | 9832133215 | TX | US |
| 4 | Ben | M | 11 | wdb@gmail.com | 9832133216 | TX | US |
| 5 | Zack | F | 12 | abds@gmail.com | | US |
| 6 | Doug | | 13 | adfb@gmail.com | 9832133218 | | US |
| 7 | | | 14 | adaxb@gmail.com | 9832133219 | | US |
| 8 | Bob | M | 15 | axfdwb@gmail.com | 9832133220 | TX | US |
| 9 | Tina | F | 16 | dddab@gmail.com | 9832133221 | TX | US |
Lookup sheet:
| Field | Mandatory |
| ID | Y |
| Name | Y |
| Gender | N |
| Email | Y |
| DOB | N |
| State | Y |
| Country | N |
| Phone | Y |
Expected Outcome:
| ID | Mandatory Filled | Mandatory Unfilled | Optional Filled | Optional UnFilled |
| 1 | 4 | | 3 | |
| 2 | 4 | | 3 | |
| 3 | 4 | | 3 | |
| 4 | 4 | | 3 | |
| 5 | 3 | 1 | 3 | |
| 6 | 3 | 1 | 2 | 1 |
| 7 | 2 | 2 | 2 | 1 |
| 8 | 4 | | 3 | |
| 9 | 4 | | 3 | |
| 10 | 3 | 1 | 2 | 1 |
| 10 | 3 | 1 | 2 | 1 |
