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

@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
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies