Formula to Return "Missing Data" Fields by Name

[kimberlyre2000]

The following is sample data set:

First Name Last Name Street City State Results Column
Jane Smith 123 Denver Write formula that would show what "category is missing". (i.e. State)
John 456 Salt Lake UT Write formula that would show what "category is missing". (i.e. Last Name)

I want to write a forumula that would identify "State" is missing in row 1 or "Last Name" is missing is row 2.

The empty spaces are blank and the data set has thousands of rows, so I would prefer not having to input text, etc. in the blank cells to make it work.

Thank you.

[Ace_XL]

One way

=IFERROR(INDEX($A$1:$E$1,MATCH(TRUE,ISBLANK(A2:E2),0)),"")

Confirm with Ctrl+Shift+Enter

[djapigo]

Hi Kimberly,

Try this formula...
=IFERROR(LEFT(IF(A3="",$A$1&", ","")&IF(B3="",$B$1&", ","")&IF(C3="",$C$1&", ","")&IF(D3="",$D$1&", ","")&IF(E3="",$E$1&", ",""),LEN(IF(A3="",$A$1&", ","")&IF(B3="",$B$1&", ","")&IF(C3="",$C$1&", ","")&IF(D3="",$D$1&", ","")&IF(E3="",$E$1&", ",""))-2),"nothing is missing")

[kimberlyre2000]

Ace,

My result field is coming back blank - do you know what would cause that?

[kimberlyre2000]

Works great! Given the large amounts of columns and data, do you know if there is a way to minimize the formula string (20 columns on real data set)?

[djapigo]

Hi Kimberly,

I just duplicated the main formula IF(A3="",$A$1&", ","")&IF(B3="",$B$1&", ","")&IF(C3="",$C$1&", ","")&IF(D3="",$D$1&", ","")&IF(E3="",$E$1&", ","") which is just small IF formulas and I concatenated with ampersands...

The reason I duplicated it is to remove the last comma...

If you can live with the last comma, then you can simplify the formula to just the one I typed here...

[djapigo]

Hi Kimberly,

OK, maybe this will help you, it's very clean, but does involve VBA coding.

Open VBA (Alt-F11), insert a Module, then copy and paste this on the coding area...

Please Login or Register  to view this content.

Then in the results column, simply type the new formula name... =FindMissing(A2:E2)

A2:E2 can be any size range and your heading is on the first row...

Let me know if this is what you need...

Take care,
Dennis