|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Trying to sort different values from different categories under other categories
Good morning,
First post. I have an excel sheet with different companies on the X axis and different job positions on the Y axis. Additionally, various job positions have different levels based on experience. If the company is hiring that job position, I input their pay into the sheet. Attachment for example, but this project is dozens of companies and 100+ job positions. Is there a way to display the position, then level, then have any non-zero companies underneath sorted by greatest to least? Attachment with desired output for reference. |
#2
|
||||
|
||||
Hi
please post a sample sheet. Pictures are useless to work with. Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
Hello,
It is now attached, I'm working with the top and trying to create the bottom through anyway faster than manually. |
#4
|
|||
|
|||
Your design is not meant for getting info from your tables easily. Consider a design I applied as example of better one.
|
#5
|
|||
|
|||
Your design is not meant for getting info from your tables easily. Consider a design I applied as example of better one.
In Salaries Table, you preferably hide the column RecNo - it being visible only confuses user. About getting info sorted, for easier cases you can use sort and filter options on Salaries Table. For more sophistcated cases, you can design report sheets, where proper Salaries Table RecNo value for report table row are calculated to hidden column in report table, and like on Entry sheet, this RecNo value is used to read wanted data from Salaries Table. |
#6
|
||||
|
||||
Which is the table with your source data?
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#7
|
||||
|
||||
Your source data is already like a pivot table so needs some re-organisation in the background before presenting it to the user.
In the attached are two offerings (both use Power Query). I've made the source data into a proper Excel table. I've also added a line to that source data to make sure that multiple positions for the same Position/Level/Company but different Pay are properly handled. 1. A Pivot table at cell G19. This is a bit of a cheat because I've hidden column J of the sheet because I'm not really using the pivot table to summarise data but to display it in a different way, so the Index column ensures that records remain separate. A bit messy. 2. A Power Query table at cell M19. This is more straightforward and more robust. There's just one tweak I made to that result table, I added conditional formatting in the first two columns to hide repeating items so that it looks a bit like what you wanted. Both solutions need to be refreshed should the source data be changed. Use the Refresh All button on the Queries and Connections section of the Data tab of the ribbon. |
#8
|
||||
|
||||
So nice when people acknowledge the help people have tried to give them…
|
Tags |
sort worksheet, table |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Sorting: Sort Categories, and Then the Items in Each Category? | tatihulot | Word | 1 | 05-12-2022 06:37 PM |
Dynamic Categories | stephen_ | Excel | 4 | 07-03-2013 01:38 AM |
Categories disappear | PattiL | Outlook | 0 | 08-05-2011 08:34 AM |
Using categories | Matelot | Outlook | 3 | 10-14-2010 07:32 PM |
Categories disappeared? | markg2 | Outlook | 1 | 08-02-2010 03:24 PM |