Sorting Hierarchical Excel Table Keeping Certain Rows/Groups Intact

**JCarWSC** · 11-04-2019, 08:46 AM

Hi!

I have an an excel sheet containing data in a sort of 'hierarchical' form.
Basically this is Energy consumption data for various zones; these are first split into different clusters, which are further split into Zones, some of which are also split into Sub-Zones.

For Example:
Cluster A---Average Value for Cluster A :10
---Zone A1 :9
---Zone A2 :10.5
------->Sub-Zone A2.1 :7
------->Sub-Zone A2.2 :12
---Zone A3 :9.5

Cluster B---Average Value for Cluster B :14
---Zone B1 :16
---Zone B2 :11.5
------->Sub-Zone B2.1 :10
------->Sub-Zone B2.2 :14
---Zone B3 :15

This is a fairly large list containing 100-200 entries. My goal here is to sort this data in a descending order while keeping the hierarchical order, in order to see areas with highest consumption.
So the Cluster with the highest average would be on top, with all the zones contained in the cluster being then sorted similarly in a descending order, and any Sub-zones within the zones being also sorted in a descending order (while still being listed under their parent zone, which is sorted and listed under the parent cluster).

Basically I first want to sort the Clusters in descending order of consumption as if they were single entries, then within each sorted Cluster 'group' I essentially want to run a second sorting, this time sorting the Zones in descending order (while keeping the parent clusters sorted as in the initial sorting), and similarly a third sorting for any sub-zones.

The given example would thus be as follows:

Cluster B---Average Value for Cluster B :14
---Zone B1 :16
---Zone B3 :15
---Zone B2 :11.5
------->Sub-Zone B2.2 :14
------->Sub-Zone B2.1 :10

Cluster A---Average Value for Cluster A :10
---Zone A2 :10.5
------->Sub-Zone A2.2 :12
------->Sub-Zone A2.1 :7
---Zone A3 :9.5
---Zone A1 :9

Is there any way that this can be achieved? I have been trying to find a good way to do this for the past hour, but I can't really come up with anything.
I might have to experiment a bit with a custom VBA macro, but I've been avoiding that as I only know the very basics heh.

**AliGW** · 11-04-2019, 08:49 AM

Welcome to the forum.

Will you please attach a small sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

Instructions (Please Read Carefully):

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment (it hasn't worked for years, and despite our repeatedly asking the technical team who own the forum to fix it, they can't be bothered to do so), so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

**JCarWSC** · 11-04-2019, 09:00 AM

This post should include an example

**JeteMc** · 11-06-2019, 09:26 AM

This proposal makes some modifications to the original data.
In column A the blanks are filled. This could be accomplished using the information in the linked tutorial.
In column B the blanks corresponding to sub zones are filled. (manually)
Columns E:G separated the values found in column D by category. (various formulas)
Columns H:J rank the values in columns E:G (various formulas)
Column K applies hierarchical numbering to the rankings using: =IF(ISNUMBER(H2),H2,IF(ISNUMBER(I2),SUM(INT(K1),I2/10),SUM(ROUND(K1,1),J2/100)))
Column L sorts the values in column K using: =AGGREGATE(15,6,K$2:K$19,ROWS(A$1:A1))
Columns M:O display the sorted categories using formulas similar to: =IF(LEN(MOD(L2,1))=1,INDEX(A$2:A$19,MATCH(L2,K$2:K$19,0)),"")
Column P displays the sorted values (from column D) using: =INDEX(D$2:D$19,MATCH(L2,K$2:K$19,0))
Let us know if you have any questions.

Sorting Hierarchical Excel Table Keeping Certain Rows/Groups Intact

LinkBack

Thread Tools

Rate This Thread

Display

Sorting Hierarchical Excel Table Keeping Certain Rows/Groups Intact

Re: Sorting Hierarchical Excel Table Keeping Certain Rows/Groups Intact

Re: Sorting Hierarchical Excel Table Keeping Certain Rows/Groups Intact

Re: Sorting Hierarchical Excel Table Keeping Certain Rows/Groups Intact

Thread Information

Users Browsing this Thread

Similar Threads

Sorting but keeping groups of rows together

update few columns in rows from another row keeping others intact

[SOLVED] Macro To copy information in a table (keeping format and nested table intact)

[SOLVED] Sort table while keeping multiple columns intact

Sorting vertical while keeping everything intact horizontally?

Sorting & keeping data intact?

keeping rows intact during sort

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions