How to Display only Populated Cells in a Printable Report

[Heavenslaughing]

This task is more complicated than it sounds. I have a very large survey data worksheet of which I wish to take sections at a time for a printable report on a different worksheet. Most of the data are quantitative, but there's also a comments section. Most people leave this blank. A few write something. I want the comments section at the bottom of the printable report only to display cells that have comments in them and to ignore blank ones. I'm not sure how to achieve this. Unfortunately, the simplest route of simply using a filter on the survey worksheet doesn't get the job done--the report survey still "sees" the blanks.

Am I describing this clearly? In the data worksheet, the Comments all go in the Comments column associated with the quantitative data in the same row of the survey with which they were entered. I want a different worksheet to show only the cells from that column that have comments in them. Because we'll be producing a very large number of reports from segments of data, I have a worksheet set up that comfortably summarizes the quantitative data in a one-page printable format. I could filter the comments and then copy-paste them, but I'm hoping to skip this step with a printable report that automatically populates when it's reading from the survey data.

Because the formulas for the worksheet only to read visible cells in filtered columns for the quantitative summary caused Excel to grind slowly with this much data, I'm copy-pasting the desired sets of rows into an otherwise blank worksheet, and the report is referencing from this instead. This is mostly irrelevant to the question, but it may make things a little easier, since there's no hoop of making the report only attend to a certain selection.

First-time post, and I haven't found a question quite like this before. I'm on MS Office 2013. Hope I'm addressing the forum correctly. Thank you!

[Pete_UK]

You could have a helper column on the first sheet which looks to see if there is a comment, and increments a unique sequential number if there is one, like this:

=IF(LEN(K2)>0,(M$1:M1)+1,"-")

assuming the comment is in column K and the helper formula is in M2. You can then use this as the basis of which records to retrieve on the other sheet.

Hope this helps.

Pete

[mikeTRON]

I would wrap a pivot table around the data, and only then filter out blank comments.

[Heavenslaughing]

Thanks for both these suggestions. While the pivot table seems like the obvious way to go, it's surprisingly clunky for creating the printable reports from chunks of data in the way I need to.

But how about this helper column idea? I'm a little confused how to use it, since I'm getting either 0s or value or reference errors, depending on which row I start the helper column and how I tweak the row references (with the column designations obviously changed to suit the worksheet).

[Pete_UK]

Post a sample Excel workbook.

Pete