Creating a list from a questionnaire table

I have a sheet with hundreds of questions, where column A is the question and column B is a number field containing the score and column C is a text field containing notes. Not all questions are answered and many are left blank. Column B may contain duplicate values (i.e. there may be multiple "10" answers if the scoring is from a range 1-10).

I'd like to create a second sheet which creates a list. This list should contain only questions which were answered on the prior sheet. The last row should contain a sum calculation

For example:

FIRST SHEET
Question 1 score notes
Question 2
Question 3 score notes


SECOND SHEET
Question 1 score notes
Question 3 score notes
SUMMARY total score


I'm having a lot of trouble getting this to work, any direction I should be working towards?
I can figure out how to use a formula to do this for one column
(=IFERROR(INDEX(BlanksRange,SMALL((IF(LEN(BlanksRange),ROW(INDIRECT("1:"&ROWS(BlanksRange))))),ROW(A1)),1),"")) where I have created the ranged "blanksrange" and "noblanksrange"
but then this leaves behind the other columns I need. Very confused!

Do you need to run this multiple times or just once?
If you only need to do it once, try this:
1. Copy the sheet (assuming you want to keep the original - if not, don't bother).
2. Select column B from row 1 to the end.
3. Press F5 to open the 'Go To' dialogue box.
4. Click 'Special'.
5. Select 'Blanks' and click OK.
6. Delete the selected rows (Home tab, Cells section, 'Delete Sheet Rows').
7. Add a SUM at the bottom of column B.

I'd be running this somewhat often.

I now have a piece of code that runs to pull data over and hide rows that are containing values I want to ignore (in the below example, everything but "3"), but the code runs very slowly and I really don't like it.

// the tab that contains my non blank list, carries over a score or inserts a blank
=IF(Questionnaire!C9=3,Questionnaire!C9,"")



// code on the new spreadsheet, hides all blank rows
Option Explicit
Private Sub Worksheet_Activate()
Dim r As Range, c As Range
Set r = Range("c1:c800")
Application.ScreenUpdating = False
For Each c In r
If Len(c.Text) = 0 Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
Application.ScreenUpdating = True
End Sub



Still looking for a better solution!

Sorry for the delay in replying - been a bit busy travelling with the family.

Try running this on a copy of the sheet, to delete the rows with blanks in column B:

Please Login or Register  to view this content.

I've read somewhere that SpecialCells can fail to work properly if the range is too big, in which case you could use this instead:

Please Login or Register  to view this content.

(Note that the second one is based on code from here: http://www.mrexcel.com/forum/excel-q...ml#post3483156, so thanks are due to the posters there, not to me)

Is that of any help?