I have an issue where I have multiple variables per group, but only want/need to top 5 for each group
For the sake of space, we'll say top 3 for this EX:
Group - Income
Group 1 - 234
Group 1 - 123
Group 1 - 35
Group 1 - 2
Group 2 - 1000
Group 2 - 2383
Group 2 - 8993
Group 2 - 334
Group 2 - 400
I can't sort by >x number due to the groups have different levels of income. And I only want the top PER group.
Is this possible to only show the top 5 for each group?
Thanks!
Welcome to the forum!
Sort how? In columns? How many and which ones? You want all other rows hidden?
Thanks,
Solus
Please remember the following:
1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.Highlight the code in your post and press the # button in the toolbar.2. Show appreciation to those who have helped you by clickingbelow their posts.
3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.
"Slow is smooth, smooth is fast."
Hi zachstein,
I'd suggest a Pivot Table.
Can you post the file?
Remember you are unique, like everyone else
They can be sorted however, I just need the top five "incomes" for each group. But yes, i would like all the incomes that are not in the top five (in terms of value) eliminated or hidden.
Thanks,
Zach
I guess what I was asking was, are the incomes listed in a certain column? Column A? Is each line of data listed above in once cell? Split into two?
What you're asking for sounds pretty straight forward but it might be easier to come up with a solution if you posted an example workbook to show how the data is laid out.
To post a sample workbook, click the 'Go Advanced' button below and use the paperclip in the toolbar to navigate to your file.
Here is the document!! Thanks everyone for the quick responses
ABCounts.xlsx
Sorry For Not Clarifying. This is Tab 2. The Groups are in Column A and the incomes are in column D. The end game is to use the Cities in column B that correlate with the top 5 incomes in D.
Thanks
Have a look at the attached, I had to use the 'Postofc' field in the value area to be able to filter just the Top 5.
This is assuming there are at least 5 cities for each group:
Please Login or Register to view this content.
Thanks noboffinme,
My only concern with this is your table came back with these as the top 5 for Group 1
2201
161
104
38
22
Where the actual top 5 are as follows:
2464
2201
1983
1543
1331
What do you think?
Solus,
How do I input that into my excel sheet.
And I do know that some groups have less than 5, will that just have to be manual.
Thank you!
Hi Zack,
No VBA needed. Simply sort by values from large to small and then create a Helper column. Look at the formula in Row 2. Create it and double click on the bottom right corner of that cell and it will autofill down to the bottom. Then create a pivot table using the helper as columns. Then only display the first/top values for each group. See the attached. Fun Problem...
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Thanks MarvinP. I assume this is possible to replicate with the actual City names as well?
Thanks a lot, this is saving me loads of time!!
Here is a version that accounts for towns that don't have 5. Open and click the button.
If you would like to view the code and where it is placed. Right click the tab for the sheet and select 'View Code'
ABCounts (5).xlsm
I added a second helper column for cities and added a second Pivot to show how easy it is.
Solus,
I ran the macro, and it's magic. Much appreciated. Any idea why excel is now flickering on and off continuously?? haha I think my computer can't handle it.
Thanks everyone
Glad to help. And welcome to the forum!
Not sure about the flicker. But I am sure I can't write a macro to fix it
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks