Home

Find and list missing fields in Excel row

%3CLINGO-SUB%20id%3D%22lingo-sub-892026%22%20slang%3D%22en-US%22%3EFind%20and%20list%20missing%20fields%20in%20Excel%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-892026%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F135360i851DE8F11340579E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22currentexcel.JPG%22%20title%3D%22currentexcel.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20with%20a%20bunch%20of%20columns%20I%20am%20trying%20to%20populate%20over%20time.%20I%20want%20to%20have%20a%20column%20listing%20all%20the%20column%20values%20in%20each%20row%20that%20are%20missing%20so%20I%20can%20see%20what%20is%20missing%20the%20most%20in%20my%20data.%20What%20function%20do%20I%20use%20to%20do%20that.%20For%20example%2C%20in%20these%20rows%20Opportunity%20ID%2C%20Quote%20ID%2C%20SAP%20DMU%2C%20etc%20should%20be%20listed%20in%20a%20separate%20column%20'Missing%20Values'.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-892026%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-892049%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20and%20list%20missing%20fields%20in%20Excel%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-892049%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F419679%22%20target%3D%22_blank%22%3E%40vakarithika%3C%2FA%3E%26nbsp%3BI%2C%20on%20the%20top%20of%20my%20head%2C%20think%20of%20two%20solutions.%3C%2FP%3E%3COL%3E%3CLI%3EIf%20the%20number%20of%20columns%20and%20the%20format%20of%20the%20sheet%20are%20going%20to%20remain%20static%2C%20you%20can%20always%20have%20a%20formula%20check%20for%20empty%20columns%20and%20concatenate%20the%20column%20names%20to%20give%20you%20that%20calculated%20field%20value.%3C%2FLI%3E%3CLI%3EA%20VBA%20macro.%20How%20I%20love%20macros!%20Its%20more%20dynamic%20in%20nature%20and%20can%20dynamically%20calculate%20based%20on%20number%20of%20columns%20and%20rows.%3C%2FLI%3E%3C%2FOL%3E%3CP%3EAlternatively%2C%20I%20wont%20be%20surprised%20if%20there%20is%20a%20way%20of%20doing%20this%20using%20pivots.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-892132%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20and%20list%20missing%20fields%20in%20Excel%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-892132%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F419679%22%20target%3D%22_blank%22%3E%40vakarithika%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20access%20to%20the%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Ftextjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3ETEXTJOIN%3C%2FA%3E%20function%2C%20you%20could%20use%20something%20like%3A%3C%2FP%3E%3CPRE%3E%3DTEXTJOIN(%22%2C%20%22%2CTRUE%2CIF(A2%3AT2%3D%22%22%2C%24A%241%3A%24T%241%2C%22%22))%3C%2FPRE%3E%3CP%3Ewhich%20will%20return%20a%20comma%20separated%20list%20of%20the%20field%20names%20in%20each%20row%20in%20another%20column.%20Hope%20that%20helped.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

currentexcel.JPG

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. 

Highlighted

@vakarithika 

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.

Related Conversations
Fonction si ?
LolitaG in Excel on
0 Replies
How do I do this?
moppy1234 in Excel on
2 Replies
FORMULA ASSISTANCE
Bran20 in Excel on
2 Replies
Excel formula auto complete not working
Bholeswar in Excel on
0 Replies
How to Include a Text Field in an Excel Pivot Table
tdc-studio in Excel on
2 Replies
Excel taking forever to launch and very slow
pjvilloud in Excel on
2 Replies