Array Formula to omit blank rows
In excel I have a survey with names appearing in column A and comments about individuals in column H. Names appear multiple times, in random order, not every survey submitted includes a comment. In a separate sheet I am compiling the results of all questions for each individual. I can write an array formula that gives me the individual rows for each individual, and the comment. What I would like to do is not return the row is the comment is blank. When the survey is completed I could to a simple sort, but the survey is asking about 30 individuals and will include over 2000 responses. All other components of the survey update as
The best solution for this is to use a Pivot Table and filter blank comments
In this example, we assume a header row and use column I as a helper column.
In I2 enter:
=IF(H2="","",1+MAX(I$1:I1))
and copy down:
Then in the second sheet, cell A1, enter:
=IFERROR(INDEX(Sheet1!A:A,MATCH(ROW(),Sheet1!I:I,0)),"")
and copy down. In the second sheet, cell B1, enter:
=IFERROR(INDEX(Sheet1!H:H,MATCH(ROW(),Sheet1!I:I,0)),"")
and copy down:
You would capture any other columns in the same fashion. Once the data has been captured, you can sort it any way you please.