Macro to format and sort data with a pattern

[prvimoon]

Hi,

I have some data that I want to get formatted/sorted in a certain way. I've included a before and after XLSX to show what I want the macro to accomplish.

Here's the structure:

Please Login or Register  to view this content.

Every sample follows the format:

------

Things I want the macro to do:

1)

Format the structure so that it skip 2 lines before every category, 1 line after every category and 1 line for sub-categories.
There are sometimes random 1 line skips in the sub-categories, which will need to be taken out.

2)

Anything that has the pattern:

Change to:

Anything that has the pattern:

Change to:

3)

Sort by:
1st) Sub-category (Apples, Oranges, Dragonfruit, etc)

2nd) Quality (peerless,legendary,magnificent,exquisite,superior,average,fine,crude)

Peerless is formatted:

Legendary is formatted:

The other quality tiers (magnificent,exquisite,superior,average,fine,crude) are as-is.

3rd) Prefix (anything that is color coded/italicized goes first, in alphabetical order)

i.e

4th) Level

4)

Insert this at the very top, line 1:

Then get the top five most expensive items and put it underneath. Prices are mostly abbreviated, m for millions and k for thousands, but there are some with the full, non-abbreviated amount. If there's a tie(s), sort it in the order of: Quality, Prefix, Level.

[prvimoon]

Bumping, thank you.

[prvimoon]

Bumping, thank you.