Find No. of fields per ID which are not filled?

[Kavya132017]

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

[binuacs · Accepted solution]

@Kavya132017 one way of doing this with the batch macro

[nagakavyasri]

@Kavya132017 

[caltang]

Like so?

[caltang]

Mine is including "ID" as a field count, so you will have 5 and not 4. But if you do not want "ID" to be counted, you can also use this:

Untick the field "ID" and you will get the result below:

[Kavya132017]

@nagakavyasri , My IDs in the original data are not in sequence. They are contract IDs randomly generated. 

example: Id- 4453, 5643,3553,9584

[grazitti_sapna]

Hi@Kavya132017 

I have made Workflow for your Problem. Please Check.

If it works Kindly accept it as Solution.

Thanks