Macro to display all non-blank cells from column if meets criteria in another column

[BethK]

Good day to all.

Thanks to this site and ExcelIsFun I’ve made great progress in creating a neat "report". You’re awesome!

Survey data for multiple classes is captured in a single source and is downloaded to Excel. Each survey response is its own row and contains the Class ID. Responses to all questions are not required, therefore cells can be blank. I have created a “report” on another worksheet to display the results for a single class. The user will be prompted to enter the Class ID which will run a macro to populate the report.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

Here’s where I’m stuck…
The survey includes a couple open ended questions and I want to display each non-blank answer for the selected class on this “report” in the rows (as many as are needed) below the question.

<o></o>
I’ve been playing with variations of INDEX and IF statements but can’t seem to figure out the right combo.

<o></o>
<o><o></o>
</o>


Named ranges:
ClassID = C1:C8
OQ1 = F1:F8

<o></o>
This array formula gives me the number of results which I was expecting to use for looping.
{=COUNT(IF(ClassID="ClassA",IF(OQ1<>"",A1:A8)))}
<o></o>

I have Excel 2003 & Windows XP.

<o></o>

Excel 2003ABCDEFG1IDResponderClassIDQ1Q2Do you have any suggestions?Another open ended question21AnnaClassA232JoeClassA22Want more hands on.43AmyClassB55No, it was a great class! Very helpful! 54MikeClassB45Would like a quick reference sheet.65DonClassA23Need more time or break into two classes. 76CindyClassB4487DougClassA22
[CENTER][COLOR=#161120][B]Sheet1[/B][/COLOR][/CENTER]

Many thanks!

 

[BethK]

Hi,

I seem to be getting closer. Here's what I've got so far.

I placed the Class to return in C10.

I can count the number using this array formula, in E10:
=COUNT(IF($C$2:$C$8=$C$10,IF($F$2:$F$8<>"",$A$2:$A$8)))

And this array formula returns the first result, in F10:
=INDEX($F$2:$F$8,SMALL(IF($C$2:$C$8=$C$10,IF($F$2:$F$8<>"",ROW($F$2:$F$8)-ROW($F$2)+1)),ROWS(H$10:H10)))

If I copy that formula down it returns the valid results first and then the expected #NUM errors.

It seems like I just need to get this into a formula that loops for the total count, inserting a row for each valid result.

I'm going to try again to copy my sample data, as yesterday I must have done something wrong. (Still learning the HTML copy thing.)

Excel 2003<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>ID</TD><TD>Responder</TD><TD>ClassID</TD><TD style="TEXT-ALIGN: center">Q1</TD><TD style="TEXT-ALIGN: center">Q2</TD><TD>Do you have any suggestions?</TD><TD>Another open ended question</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">1</TD><TD>Anna</TD><TD>ClassA</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD>Joe</TD><TD>ClassA</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">2</TD><TD>Want more hands on.</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">3</TD><TD>Amy</TD><TD>ClassB</TD><TD style="TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: center">5</TD><TD>No, it was a great class! Very helpful! </TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">4</TD><TD>Mike</TD><TD>ClassB</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">5</TD><TD>Would like a quick reference sheet.</TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right">5</TD><TD>Don</TD><TD>ClassA</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">3</TD><TD>Need more time or break into two classes. </TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right">6</TD><TD>Cindy</TD><TD>ClassB</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right">7</TD><TD>Doug</TD><TD>ClassA</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>

Sheet1​

Any help is greatly appreciated, thanks!

 

[BethK]

Thanks to a lot of persistence, Google, ExcelIsFun, MrExcel, and insomnia : ) and I came up with a solution that - so far - works. Yay! As this is outside my normal job, I'm sharing it in case anyone sees something I haven't considered.

Sub PopulateResponses()
'Create Class ID name
    Sheets("Data").Select
    Rows("1:1").Select
    Selection.Find(What:="ClassID", LookIn:= _
          xlFormulas, SearchOrder:=xlByRows).Activate
    ActiveCell.Select
'    newIDrange = ActiveCell.Address
    Range(Selection, Selection.End(xlDown)).Select    'highlights the entire range.
    Application.DisplayAlerts = False   'Temporarily disable error handling
    On Error Resume Next
    ActiveWorkbook.Names("ClassID").Delete
    Application.DisplayAlerts = True    'Reinstate error handling
    On Error GoTo 0
    Selection.Name = "ClassID"
    howManyQuestionResults = Selection.Rows.Count
        
'Call ResetOpenQResponses  'clears responses, resets formatting in case this macro is run more than once
        Sheets("Report").Select
    'store the class number to search
        Dim CforRpt As String
        Range("A2").Select
        CforRpt = ActiveCell.Value
    'Remember the open ended questions - questions are always in the same position
        Range("A5").Select
        OpenQ1 = ActiveCell.Value
        OpenQ1A = ActiveCell.Address
        Range("A9").Select
        OpenQ2 = ActiveCell.Value
        Range("A13").Select
        OpenQ3 = ActiveCell.Value
    'leave cursor in the cell to receive the response for the first open question
        Sheets("Report").Range(OpenQ1A).Offset(1, 1).Select
    'in the ClassID field, count/store the number of rows, used for the end of the range to search the classID
        Sheets("Data").Select
        Range("ClassID").Select
        lastrow = Selection.Rows.Count
        
        cCID = ActiveCell.Column
                
        Qnbr = 1
    
    Do While Qnbr < 4   'loop here per question
        If Qnbr = 1 Then OpenQ = OpenQ1
        If Qnbr = 2 Then OpenQ = OpenQ2
        If Qnbr = 3 Then OpenQ = OpenQ3
            
    'set/store numbers
        Sheets("Data").Select
        Rows("1:1").Select
        Selection.Find(What:=OpenQ, LookIn:= _
              xlFormulas, SearchOrder:=xlByRows).Activate   'find the question
        cOQ = ActiveCell.Column   'remember the column that has the question
        cto = cOQ - cCID  'is columns to offset, to get to question response
        Dim r, c
        r = 1
        c = 0
        resp = Application.WorksheetFunction. _
            CountIf(Worksheets("Data").Range("ClassID"), CforRpt)  'count the number of responses for the class
    Do While c < resp   'loop here per answer
        c = c + 1
    
    'find the first/next instance of the class number
        Sheets("Data").Select
        Set searchrange = Range(Cells(r, cCID), Cells(lastrow, cCID))
        Set n = searchrange.Find(What:=CforRpt, LookIn:= _
            xlFormulas, SearchOrder:=xlByColumns)
        latestfind = n.Address
        r = n.Row
        Response = Range(latestfind).Offset(0, cto).Value
    'put non-blank response on single class report
        If Not IsEmpty(Response) Then
            Sheets("Report").Select
            Selection = Response
            ActiveCell.Offset(1, 0).EntireRow.Select
            Selection.Insert Shift:=xlDown
            ActiveCell.Offset(0, 1).Select
        End If
        
    Loop  'end of loop for responses
        
        Sheets("Report").Select
        ActiveCell.Offset(1, 0).EntireRow.Delete
        ActiveCell.Offset(0, 0).EntireRow.Delete
        ActiveCell.Offset(2, 0).Select
    
        Qnbr = Qnbr + 1
        
    Loop   'end of loop for questions
    
    Sheets("Report").Select
    Range("A1").Select   'put the cursor back at the top of the report
End Sub

FYI, there are 3 open ended questions and the "raw" report is formatted for two responses per question which I wanted to preserve as the responses are pasted.