Returning 'n'th value in a text array

[fjslayer]

Hello all,

I have been trying to find some formula which will assist me in getting something like a Top 10 listing done.
I seem to have found everything but what I am after.

Kindly note below, the below column G has a list of drop-downs coming from another worksheet in the file.

page1.jpg

What I'm after is to have a top 10 on the 'Analysis' page to return the top 10 values of repair categories and count them in the next column.
See below:

page2.jpg

It seems so simple to me but I have tried many various functions and combinations, either I get a silly value or and error.
Please take a look at this and let me know how I can achieve this - even if I need a helper column, I'm fine with it but I need this to work properly even if values are tied, meaning TV is 10 and fridge is 10 for example...

Should you need any other formation, please inform me.
Thanks a lot in advance, hope my answers for now and future lie in this forum!

[Glenn Kennedy]

please attach your excel sheet...

[fjslayer]

Thanks Glenn for your quick reply...

See attached, I have taken about two weeks to get this done for my firm.
It's still a work in progress but I think it's well done.

Any comments appreciated...
Thanks in advance!

[Glenn Kennedy]

You've clearly put a lot of effort into this!!

I added a couple of Named Ranges (CTRL F3 to vIEW) to make the formulas a) work and b) be dynamic, extending the range every time you add another job. i also changed the name of one sheet (job listing) to joblisting. If that causes you any major problems, let me know.

For the ranking, I used four array formulas. They're a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

I didn't understand what you wanted int he last section (shaded greenish). However, I'm sure you can figure it out for yourself!!.

[fjslayer]

Hi Glenn,

Yes, it has taken quite a long time to figure the whole thing out.
Everything works like a charm - it's great!

However, can you please explain the formula you have used?
=IFERROR(INDEX(Jobs,MODE(MATCH(Jobs,Jobs,0)+{0,0})),"")

I kind of understand it, but not 100%, would help in the future I think... :-)


Thanks a million...



Eugene

[Glenn Kennedy]

Hi. This'll explain what is going on, better than I could!!
http://www.excel-easy.com/examples/m...ring-word.html