I have a column with great numbers of cities mentioned
visby
visby
stockholm
stockholm
stockholm
uppsala
uppsala
uppsala
uppsala
The column consist with up to 1000 rows with cities, about 50 to 100 different cities.
Want to pick up the top 5 common cities and numbers for these top 5.
Is that possible?
SEALPINO
Create a simple Pivot Table which just counts the cities then Filter the top 5
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Dear sealpino ,
Refer attach file.
In this file i have create column "A2:A51" Name
In column "B" count withFormula:
Please Login or Register to view this content.
IN column "C" most common with formula :
Formula:Please Login or Register to view this content.
In column "D" top 5 with formula :
Formula:Please Login or Register to view this content.
All formula drag down till end.
if your range differ then you can change accordingly.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
Example of analysis using a Pivot Table ...
I got the formule concept.
Is it possible to avoid this extra column B,
I would like to have it straight in one box. for example "F1"
Have issues later on when connecting other workbooks into this new workbook.
SEALPINO
You have marked the thread as solved. If you want more help, then remove the solved tag from your opening post.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I am now using the solution with attached file Most_CommonTop5.xlsx made by AVK in this forum. Gratitude to him
It works brilliant and it works in my xlsx format the way I am using it.
However now I would like to add up a funtion to see the the split of females (1) and males (2) with the exception only present those with age older than 18.
THANKS IN ADVANCE
Paste the following formula into cell G2, then copy down and across:Let us know if you have any questions.Formula:Please Login or Register to view this content.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Hi It comes up a dialog box stating that there is some error in the formula. Try to check with Fx and see if it works.
It does not.
How about the parantheses are they correct?
Seems to work fine on a copy of your spreadsheet (attached)
Let us know if you have any questions.
Thanks for yur kindness.
It works.
The thing was that I have not an english version so it could not work. But when you put it in ittranslated correctly.
THANKS. I tried to add an reputation but did not work. Have to add more rep to others....
WELL THANK YOU JeteMC.
You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks