0

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

  • You can't really do this with a formula unless you write your own in VBA – airstrike Jan 8 '17 at 3:36
1

The best solution for this is to use a Pivot Table and filter blank comments

| improve this answer | |
1

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:

enter image description here

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:

enter image description here

You would capture any other columns in the same fashion. Once the data has been captured, you can sort it any way you please.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.