Good morning community,
I would like to find the top 5 highest value and the top top 5 lowest value for France.
I would like to obtain the results with the following display:
Capture.PNG
The data is in the sheet "Raw data inputs".
Thank you for your help.
Last edited by Luu4466; 11-26-2020 at 05:30 AM.
The attached does this for you.
There are two sheets for the top/bottom 5. When your raw data changes you just need to right click on these tables and choose 'refresh'
Note the bottom 5 have no value, if you wan the bottom 5 with a value let me know.
Say thanks, click *
How did you make this because i don't see formulas.
Did you copy paste raw data inputs and paste this in the new sheet? t
Then, did you create a filter?
Does this filter can update if i change data in my raw data?
Last edited by Luu4466; 11-25-2020 at 05:58 AM.
Oh yeah sorry didn't provide an explanation!
So this is done using Power Query.
You will need to look online/youtube for some started guides to help you understand this better.
Steps I did to get this working on yours:
On the Data ribbon select Get Data>From File>From Workbook.
Choose your file.
Select the Raw Data sheet and click Transform Data. A new window will appear with a table showing your raw data - This is the Power Query Editor where you can make changes to the data then save it (it's sort of like recording a macro i guess).
The section on the right shows all the steps applied to the data to get it looking like it does. Keep an eye on this as you make the changes below.
First of all from the ribbon click on 'Choose Columns'. Unselect all then select just the ones with data. Click OK.
With 'Country' click on the filter and choose France.
Scroll to the price column and sort by value high to low.
On the ribbon select Keep Rows>Keep Top Rows. Type in 5 then OK to keep the top 5.
Close this window and save changes. A table will be created on a new sheet.
Repeat the above for the bottom 5 but sort the data low to high instead.
When the raw data changes you just right click on each table and choose refresh to update it.
If you need to make changes to these tables then select the table and head to Query>Edit to get back into the editor view.
Also realised my example file won't refresh for you because it is looking in the wrong place for the excel file, have a go at starting from scratch to see if you can get this to work following the above steps.
Any issues let me know.
PowerQuery will help you to automate a lot of data manipulation so it is worth looking into even if only for basic tasks to save you some time.
Last edited by Harribone; 11-25-2020 at 07:34 AM. Reason: Typo
Thank you for you entire explanation. It's great, you solved my problem.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
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.
Yeah. I added reputation for @Harribone and put thread as solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks