How to know which column has the highest cells left blank

mwender

New Member
Joined
Jun 13, 2018
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi,
I have a table with 20 columns and ~700 rows. A new raw is set for a new project. Each column is depicting the project details.
I'd like to count the number of blanks for each column and get it summarized (pivot table?) so I can draw a pareto chart and find easily the column with the highest number of blanks.
That way I'll be able to come back to the project owner with the highest number of cells left blanks and ask him to edit.

Any suggestion on how to do that?
Thanks
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
where are you putting the output for the formula? On the top row above the first row? In a new worksheet?
 
Upvote 0
I can't be as a new row as each row is a project...
I was thinking getting the output in a pivot table in some way
 
Upvote 0
Well, you could just do a pivot table selecting only blank values, but I'm not sure how to do that with an OR mentality.
There is probably a way to make a formula based table I'll try to mock something up.

In the meantime, if possible, can you use the xl2bb add in to paste a mini workbook of your data? Besure to choose mini workbook include all the options,
And when copying please select the range before clicking the mini workbook icon. (you'll understand when you install and see the add in on the ribbon).
 
Upvote 0
also, what version of excel are you using?
 
Upvote 0
if you are using 365, then this give you a crosstab report of missing details per project:
It is a clunky formula, and there are probably better ways to get it, but it works. It could probably be easily done with a LET or LAMBDA function:

mr excel questions 17.xlsm
ABCDEFGH
1ProjectDetail1Detail2Detail3
2Project1161423Project5Detail1Detail3
3Project210249Project10Detail1Detail2
4Project3211325Project13Detail1
5Project420168
6Project514
7Project65258
8Project762521
9Project8252523
10Project924922
11Project1025
12Project117716
13Project12152114
14Project131813
15Project1423218
16Project1524912
17
mwender
Cell Formulas
RangeFormula
F2:F4F2=TAKE(FILTER(A2:D16,(B2:B16="")+(C2:C16="")+(D2:D16="),""")),,1)
G4,G2:H3G2=INDEX($B$1:$D$1,1,FILTER((--(DROP(FILTER($A$2:$D$16,$A$2:$A$16=$F2,""),,1)=0))*(COLUMN(B2:D2)-1), (--(DROP(FILTER($A$2:$D$16,$A$2:$A$16=$F2,""),,1)=0))*(COLUMN(B2:D2)-1)<>0,""))
Dynamic array formulas.
 
Upvote 1

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top