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!
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!