Finding Deficiences

[Jarvin24]

Hi Team,

Good morning.

I am hoping someone could assist me with my problem.

I have a summary of all customers (Data tab) which contains a checklist of the documents they submitted. If there is no check on any of the DOCUMENT COLUMN, then it means that customer is DEFICIENT with that document.

What I need is to find those customers and create a summary of their deficiency (Please see sample file for your reference).

Is it possible using PIVOT or Formula? or there is really no choice but to use VBA?

Appreciate your response.

Thank you

[JeteMc]

See if the following does what you want:
1) Select cell B2 on the Summary sheet,
2) Paste the following array entered formula into the formula bar:

Please Login or Register  to view this content.

3) While B2 is still in edit mode, simultaneously press the Ctrl, Shift and Enter keys,
4) Copy down and across.
Let us know if you have any questions.

[Jarvin24]

Hi Jet!

Thank you buddy for your response, this is exactly what I was looking for but it seems this formula only captures ONE DEFICIENT document. Looking on my example, BOY 4 have 3 deficiencies, with this formula, it only captured DOCUMENT 2.

Thank you

[JeteMc]

Here is a copy of the file with the array entered formula applied, it shows 3 deficiencies in row 5.
Let us know if you have any questions.

[Jarvin24]

Thanks Jet!

I'll read the formula and hopefully apply it moving forward with my other problems.. ehehhe

Thanks again

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.