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
vakarithika
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
Highlighted

@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. 

@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
Average set of values if 2 criteria are met
jtorrens in Excel on
1 Replies
Refresh query to add new columns
Caesarus7 in Excel on
0 Replies
Frozen excel project
Mrkeats in Excel on
0 Replies
Macro to Import Data from PDF attachments (Outlook)
calof1 in Excel on
0 Replies
Text to Column (changing delimiters)
John11 in Excel on
0 Replies