Finding the top 3 most common results in a list and presenting them in a separate sheet

**diggers16** · 03-27-2013, 06:04 PM

Hello,

I am currently trying to organise a student yearbook for my pupils at school. We have given out sheets to students in order for them to nominate their peers for various awards such as 'Funniest Student', 'Most Caring Student' etc.

I am trying to collate all of the data into an excel workbook. I have made two sheets within a workbook, which I have formatted how I want them to look like. I want to insert all the student's responses into one sheet, and somehow, the top 3 most occurring nominations are found and are inserted into the appropriate cells in the separate sheet.

I have attached a copy of the workbook to help explain what I mean, and it will probably be clearer to you after you have seen it. Due to data protection the student names and tutor groups have been taken out and replaced with the word "REMOVED", yet I will paste them back in at a later time. So feel free to have a play around to see what I am talking about!

I am not at all sure how I would go about picking out the top 3 most occurring names for each award, but once I can do that, I will be able to insert them into the other sheet. So if anyone knows how to automatically produce the top 3 most occurring names, if you could let me know, that would be amazing!

Any help would be gratefully received!

Thanks in Advance.

**icestationzbra** · 03-27-2013, 08:27 PM

hi there,

i understand privacy, but you gotta give us some data to play with :-)... anyway, i found a website that generates names randomly - http://listofrandomnames.com/

that said, your spreadsheet is a haven to the Top 3 Excel sacrileges that i can think of - merged cells, more merged cells, and well, technicoloured merged cells...

so, i had to fix the portion where i provided a solution. your particular problem will take an ARRAY formula to solve (to the extent that my knowledge of Excel goes), and array formula does not tango with merged cells.

so, i un-merged the ones where i had to put in a formula, and instead used a beautiful and friendly substitute called "Center Across Selection" formatting - it looks and talks like a merged cell, but behaves like a gentile.

my other recommendation is that you create Named Ranges (such as funny_boy, funny_girl), instead of using regular ranges (such as A1:A10, B1:D25). in your specific case, they hold great utility because you have so many categories, and each category has 2 sub categories, and they are spread all across the worksheet, so much so that dealing with regular ranges will throw your head into a tailspin.

please see attached and adapt the formula to other sections of the worksheet, if this solution addresses your problem.

best.

**diggers16** · 03-28-2013, 12:47 PM

That is literally just what I was after, Perfect! Thank you so much!

What you have done for the 'Funniest Student' category is just what I wanted, however I am not really sure how you did it, and how I could go onto do the same with the other categories. Would it be possible for you to send/write a simple step by step guide for me as to how I would be able to do it?

Thank you again!

**diggers16** · 04-14-2013, 08:56 AM

Does anyone else have any possible solutions?

**diggers16** · 04-14-2013, 08:58 AM

bump no response

**Fotis1991** · 04-14-2013, 09:04 AM

In B2 in the winner sheet(after the unmerging of the cells-because ARRAYS formulas don't like merged cells-put this ARRAY formula and copy down to B4

=INDEX(Responses!$D$3:$D$1000, MODE(IF((Responses!$D$3:$D$1000<>"") * ISNA(MATCH(Responses!$D$3:$D$1000,Winners!$B$1:B1,0)),MATCH(Responses!$D$3:$D$1000,Responses!$D$3:$D$1000,0))))

Same way for the others.

**diggers16** · 04-14-2013, 09:58 AM

Thank you for your help.

I have done this, however, the result is always '0' no matter what I put into the columns.

I would try and have a play around with the data itself, however I don't know what references do what with the formula you have given me to help find the problem!

**Fotis1991** · 04-14-2013, 12:28 PM

Perhaps a sample will be usful for you.

Finding the top 3 most common results in a list and presenting them in a separate sheet

LinkBack

Thread Tools

Rate This Thread

Display

Finding the top 3 most common results in a list and presenting them in a separate sheet

Re: Finding the top 3 most common results in a list and presenting them in a separate shee

Re: Finding the top 3 most common results in a list and presenting them in a separate shee

Re: Finding the top 3 most common results in a list and presenting them in a separate shee

Re: Finding the top 3 most common results in a list and presenting them in a separate shee

Re: Finding the top 3 most common results in a list and presenting them in a separate shee

Re: Finding the top 3 most common results in a list and presenting them in a separate shee

Re: Finding the top 3 most common results in a list and presenting them in a separate shee

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions