Survey Submission Results - Only Allow Submitter to Answer Question Once (Column)

[AaronChiles]

I've stretched my brain enough on this problem, figured I'd finally sign up and ask the experts. I have a set of survey data:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Submitter[/TD]
[TD]Q1[/TD]
[TD]Q2[/TD]
[TD]Q3[/TD]
[TD]Q4[/TD]
[/TR]
[TR]
[TD]aaronchiles[/TD]
[TD]Pear[/TD]
[TD]Apple[/TD]
[TD]Banana[/TD]
[TD]Peach[/TD]
[/TR]
[TR]
[TD]aaronchiles[/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]johndoe[/TD]
[TD]Apple[/TD]
[TD][/TD]
[TD]Pear[/TD]
[TD]Peach[/TD]
[/TR]
[TR]
[TD]johndoe[/TD]
[TD][/TD]
[TD]Banana[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Each submitter can submit multiple times, but only once per question. If they submit multiple times per question, only the first entry is valid, the others are discarded. The data above would be valid, with the exception of Apple highlighted in red because Submitter 'aaronchiles' has already submitted Pear for Q1.

The data will be updated daily, so I'd like to find a solution that I can just paste in data...run a script or Drag-Fill a formula and have it work its magic. Thus far, I've thought about doing helper columns, but I don't think that's such a viable solution as the actual data has 12 questions. Any VBA gurus have a better solution? This looks promising, but I would need to have it run on 12 column ranges: http://www.mrexcel.com/forum/excel-questions/76464-allowing-only-one-cell-column-have-value.html

I'm eventually looking to create a Pivot Table that follows a similar format as below:

Question
Q1
--- Pear
--- Apple
Q2
--- Apple
--- Banana
Q3
--- Banana
--- Pear
Q4
--- Peach (2)

 

[excelVBAmaster]

Hi Aaron,

Here is the solution assuming that your table starts in A1 AND your data is sorted by column A

Sub Validate_Table()
    For Each Cel In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        If Cel.Value <> Cel.Offset(-1, 0).Value Then
            Q1 = ""
            Q2 = ""
            Q3 = ""
            Q4 = ""
        End If
        If Cel.Offset(0, 1).Value <> "" And Q1 = "" Then
            Q1 = Cel.Offset(0, 1).Value
        Else
            Cel.Offset(0, 1).Value = ""
        End If
        If Cel.Offset(0, 2).Value <> "" And Q2 = "" Then
            Q2 = Cel.Offset(0, 2).Value
        Else
            Cel.Offset(0, 2).Value = ""
        End If
        If Cel.Offset(0, 3).Value <> "" And Q3 = "" Then
            Q3 = Cel.Offset(0, 3).Value
        Else
            Cel.Offset(0, 3).Value = ""
        End If
        If Cel.Offset(0, 4).Value <> "" And Q4 = "" Then
            Q4 = Cel.Offset(0, 4).Value
        Else
            Cel.Offset(0, 4).Value = ""
        End If
    Next
End Sub

 

[AaronChiles]

Thank you! Works perfectly, I owe you a virtual beer!

 

[excelVBAmaster]

Haha! Good to hear virtual cheers man!