Forum Discussion
Find and list missing fields in Excel row
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
If you have access to the https://support.office.com/en-us/article/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c 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.
vakarithika I, on the top of my head, think of two solutions.
- 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.
- 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.
