Top 5 cities with multiple occurance (For dynamic dashboard without using pivot)

[DEVISREE SAIKUMAR]

Hi there.

I am creating a dynamic dashboard for a client where I have added charts and graphs with formulas (no pivot tables) so that when client add data into master data table all visualizations in summary page get updates automatically . But the issue is where I need a bar chart which shows top 5 cities from the address field where there are multiple occurrences. As am creating a dynamic dashboard I'm looking to use a formula to do this instead of pivots.

The excel shows the cities in my data and I need a formula to pull top 5 cities which are Croydon, Birmingham, Liverpool, Leicester, Bradford along with the count. Please help!

 

[Chihiro]

Pivot can be used for dynamic dashboards. I often use it for Top x charts. Have a read of link below.

https://chandoo.org/wp/dynamic-dashboard-video-tutorial/

To make pivot table dynamically update, use Excel table or set up dynamic named range as source for the pivot table.

Without sample workbook that has your data set up, hard to give you solution that fits your need.

 

[DEVISREE SAIKUMAR]

Thanks for your reply, but if you use pivots you need to refresh it when new data adds? hope I am right? ; in my case these data is been updating by solicitors in the firm who have zero knowledge about Excel, so what they do is they just update the master data and dashboard should update automatically reflecting the changes in data. I have read some similar thread here but the solution isn't clear . I am pretty sure that someone here knows the solution for this using match and index functions Due to security reasons I cant share the data ; the dataset is that simple its just has got city fields as that in the file I have attached here ; just need a formula which pulls top 5 cities including the multiple occurrence. hope I explained enough. Many thanks for your time in advance.

 

[Chihiro]

I'd do it in two stages then.

1. Extract unique list of cities, and it's count.
2. Extract top 5 list.

See attached.

 

[Chihiro]

FYI - I forgot to do it in sample, but I'd normally replace range with dynamic named ranges for ease of maintenance.

 

[Peter Bartholomew]

DEVISREE SAIKUMAR
I think this does what you have specified but it is so alien to anything else you are likely to have in your workbook, I cannot conceive of your using it!

Notes: 1. It is based on an array formula built using defined Names.
2. The City.count array uses MATCH to ensure each city is ranked only once.
3. Instead of picking the 5 largest counts directly, the value ranked is
= 1000000*City.count + RecordNumber.
The sequence is the same but I am able to pick out the indices for the cities to be returned.

Maybe it is time to reconsider the use of Pivot Tables?

 

[DEVISREE SAIKUMAR]

thanks Peter , you are really brilliant , but I don't have that courage to use this in my report thinking about the maintenance , I don't have that excel brilliance to handle this complicated formulas to be honest. I know pivot works better in this case but just took it as challenge to find a one line formula to solve this thanks again for your brilliance and help.

 

[Peter Bartholomew]

Hi @DEVISREE
Do not allow yourself to be overawed! The workbook is a bit like a tree house; you look up and wonder "how did they get there?"
What is missing is the fact that the tree house was built using scaffolding and ladders. Even when in use, a rope ladder was used.

It only looks unachievable once the rope ladder is pulled up!

In this case it is the formula that was build step-by-step on the worksheet. On each row, MATCH is used to return the row on which the City first appears. Next compare that row number with the current row to identify first occurrences.

If it is a first occurrence count the total number of city names that match. Then pick out the largest numbers. Embedding the row number in the count is devious but not impossibly so.

Packaging the formula away out of sight is where the magic happens; but it is not really magic, just conjuring!