Finding top 10 smallest data and matching it to associated data

[mightybracket123]

Hello,

I'm wondering if someone can help.

In my example data set I have a list of people that staff go and visit in varoius citys and the amount of time spent with them each meeting. An average meeting time is then created, and I want to display the 10 people where the least amount of time spent with them - this I have already done in column K.


However, in the columns highlighted in orange and yellow, I need to pull out the data that goes along with it e.g. rank 1 has the average time of 25 and this would be for Bob in London (and also Eve in Bristol). But I'm not sure how I go about pulling this associated data across. Is it by somehow combining an index match with the ranking funciton?


I'm aware that there are duplicates where the averages are the same (and yet one is assigned rank 1 and the other as rank 2) but this is another issue I need to overcome another day.



Any advice or input would be very much appreciated !

Thanks

[crazyforexcel]

Hello,

Why not make a table and put Rank in first column then sort?

[mightybracket123]

hi crazyeddie,

great suggestion! But the thing is, I've been asked to set this up so that there is this summary table to show the "top 10" which is seperate to where the data itself lives. Additionally, this summary table needs to be dynamic in as much as, after each month when we put in more meetings / add more clients, it will automatically update.

Thanks

[crazyforexcel]

Okay let me take a look.

[ThxAlot]

Hi, TABLE and Structured Reference come into play in your case.

1. Insert "Table" in the range of your dataset and name it "DS" for simplicity; what's more, u can expand the records as much as u want, whether vertically or horizontally.

2. Again I use a name "GRID" for simplicity of formula constructing; in fact, it's an unsorted array of average meeting time;

Please Login or Register  to view this content.

3. Formulae for matches based on average meeting time are constructed in the colored range.

[Pepe Le Mokko]

Like this?

[crazyforexcel]

Hi mightbracket123,

I think maybe this will do.

[mightybracket123]

Hi ThxAlot , thanks for taking a look ! What you have done is exactly what I'm after, I'm just trying to understand the logic of the formulas at work. Where you have the MATCH function included in the formulas in the coloured sections, what exactly are they matching ? (I'm only familar with more simplistic INDEX MATCHES).

Thank you again !

[mightybracket123]

Hi crazyeddie, thanks for this !! This also works and I feel I can understand much more what is going on with this. Thanks for taking a look and helping with this!!

[crazyforexcel]

This has the output also on the same sheet if you prefer with more simplistic formulas.

[crazyforexcel]

Hi again,

Maybe a bit over the top but I thought of some kind of tie breaker. A rating system to reduce the times by how the customer feels for each visit. I hope you find this useful but if not just disregard.

CE

[mightybracket123]

Hi crazyeddie

Thanks again for this and thanks for posting this tie breaker ! I'll have a think about the rating system as whilst I don't think the scoring would work exactly like this, I think the logic behind what you've done could certainly be useful, so thank you

Thanks for all your help with this I really appreciate it.

[crazyforexcel]

Maybe try this. I enjoy the challenge.

[Pepe Le Mokko]

I enjoy it too, but the OP did not care to read nor acknowledge my solution...
Another candidate for my "Ignore list" ...

[mightybracket123]

Sorry Pepe Le Mokko, I had started typing out a reply to you earlier but got caught up in work. I took a look at what you did but sadly it wasn't quite working in the way I had hoped, as where more than 1 client had the same average, it was bringing back the same location and name for both of these, rather that it being unique to each member.

e.g. cells L4 + L5 both say Bob - but one should say Bob and the other Eve

[mightybracket123]

Thanks crazyeddie. I'm forever amazed at people's ability to come up with these kinds of things. I'm slowly getting better at Excel, but every time I come on here I feel like a complete novice all over again. Thanks again for going one step further with this, it's defintely given me something to think about and how this can go to the next level.

[crazyforexcel]

This version has data validation for the ratings. It also formats the Average time spent to 2 decimal places to reduce the duplicate occurrences.

[crazyforexcel]

Glad to help