VBA Macro to Sort and Label Metrics

Hello again!
I have attached a file Example Workbook.xlsx which details what the metrics look like before this macro runs, and what the metrics should look like after.

Essentially, I want to create a macro that automatically sorts and re-labels a list of metrics each time a change is made, whether that be inserting a row, or changing one of the metrics labels. Each individual metric is listed a different row, and it is defined by labels listed in the first row of the worksheet. Each of the labels describe a single column (see attached)

Here are the following labels, and the conditions I want to impart to them:

Sort by: Priority - Order: Smallest to Largest
Then by: Category - Order A to Z
Then by: Sub - Category Order: A to Z
Then by: Metric Order: A to Z
Then by: Sub - Metric Order: A to Z

**For the sake of ease I refer to the metrics as rows

Now, you may be thinking to yourself, "This is easy! You can just use the sort command in Excel!" HOWEVER, it gets more complicated...

If 2 (+) rows of have the same 'Metric' Label, they should automatically be grouped together, no matter what the other labels and their sort priorities are. This group should be sorted according the the conditions above, and then be treated as a single metric to sort relative to the rest of the list. Also if a sub-metric in a group is labeled either "Total" or "Other", it should automatically be placed as the bottom most row of the group (not the entire list).

After the sorting takes place, the metric should take a look at the left most column labeled "ID", and label each of the metrics with a prefix and a number (ie: "ID-3") in ascending order. HOWEVER, if the each of the row has the same "METRIC" label, it should name each one in that group "ID-3A, ID-3B, etc."

Alright this pretty much sums up what I want to do. I am not too experienced with VBA, so I appreciate your feedback/ any code to help me get started...

Thanks

Originally Posted by Leith Ross

Hello DJL_DASH,

Welcome to the Forum!

To better illustrate your problem, you should post your workbook. It will make clear what you are referring to in your post. You should also include before and after examples of the data and comments about results you expect.

I posted the workbook above as an attachment "Example Workbook.xlsx"... are referring to posting a before and after image?

Any suggestions?