Find and list missing fields in Excel row

Occasional Visitor


I have a spreadsheet with a bunch of columns I am trying to populate over time. I want to have a column listing all the column values in each row that are missing so I can see what is missing the most in my data. What function do I use to do that. For example, in these rows Opportunity ID, Quote ID, SAP DMU, etc should be listed in a separate column 'Missing Values'. 

2 Replies

@vakarithika I, on the top of my head, think of two solutions.

  1. If the number of columns and the format of the sheet are going to remain static, you can always have a formula check for empty columns and concatenate the column names to give you that calculated field value.
  2. A VBA macro. How I love macros! Its more dynamic in nature and can dynamically calculate based on number of columns and rows.

Alternatively, I wont be surprised if there is a way of doing this using pivots. 



If you have access to the TEXTJOIN function, you could use something like:

=TEXTJOIN(", ",TRUE,IF(A2:T2="",$A$1:$T$1,""))

which will return a comma separated list of the field names in each row in another column. Hope that helped.