Return column names of three highest values (with ties!)

[btucker]

I'm working on matching ~2000 survey participants with 1 of 15 election candidates based on (amongst other criteria) their rating of how important different issues are to them.

There are 10 issues (A-J), but to keep it simple I want to match based on the top 3 issues (i.e. get best fit(s) for the survey participant based on the candidate that has the most issues in common in their top 3). To do this I need to gather the top three issues for each participant.

The issue is that there are many ties in the data (participants were asked to rank issues from 1-4) - is there a way to return the top three valued issues accounting for ties? (e.g. for row 2 it would return B,C,D,E,F and for row 4 it would return B,C,D?)

(fyi: I have tried to adapt the answer kindly posted here by Aladin (http://www.mrexcel.com/forum/excel-questions/469306-return-column-header;-max-value.html) but have found it returns the same column repeatedly if there is more than one answer of the highest value)

If what I'm asking about is not possible, how would I instead use INDEX & MATCH (or other formulas) to return all of the column names with the rating four?


[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

Thank you very much for your time!

 

[JLGWhiz]

Probably have to use something like:

Dim fn As Range, fAdr As String
If Application.CountIf(Rows(1), Application.Max(Rows(1))) > 3 Then 'Check how many columns contain the Max number
    Sec fn = Rows(1).Find(Application.Max(Rows(1)), , xlValues) 'Use Find/Find Next to get the column numbers
        If Not fn Is Nothing Then
            fAdr = fn.Address
            Do
                MsgBox fn.Column 'you can list these somewhere or add code to do something with them.
                Set fn = Rows(1).FindNext(fn)
            Loop While fn.Address <> fAdr
        End If
End If