VBA - Sort Data in Multiple Categories / Subcategories and Output into New Worksheet

[Aqualung]

Hey guys -


I have a bit of a complex question but I am trying to do some sorting / formatting with VBA and write a macro to help me sort through some large volumes of data. The dShouldata is stored in a table similar to what is shown below. A few key notes about the data:


The Data

• Column A has unique identifiers for all the data;
• Column B has the Category 1 name. There are 8 of these in total;
• Column C has subcategory names - Category 2. There may be 10-100 of these in a dataset, but in all cases they are mapped to Category 1 and consistent.
• Column D has a rank of all the Unique IDs within Category 2;
• Column E (through AZ) has actual numerical values for all of these unique IDs.

My Goal


I would like to re-order this entire dataset based on the following:

• First, Category 1 - for simplicity, we can assume that the 8 names within Category 1 will always be in the same order and can be ordered in some other table manually.
• Then, I would like to sum up all balances that add up to the Category 2 names, and order all Category 2 names in largest to smallest format under Category 1 headings;
• Under Category 2, I would like to order all Unique IDs based on their ranking in column D.

There are 2-3k entries to be sorted in this dataset. I would like to have this output to a new worksheet.


I'm a bit unsure where to start as I am a beginner with VBA. I've read some suggested websites and searched the internet all day today but haven't figured this out yet. Automated Excel was a good resource and I would welcome any other resource that the community thinks might help me with this issue. I figure that if I can come up with something that identifies (1) the unique category 2 names under each category 1 name, (2) their respective ranking, and (3) max of column D, then I can output those to a new range and loop through until I exhaust both category 2 and category 1 names, but I haven't figured out how to do that yet...


Any help is appreciated!

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Unique ID[/TD]
[TD]Category 1[/TD]
[TD]Category 2[/TD]
[TD]Ranking of $ value in Category 2[/TD]
[TD]$ value[/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD]1A[/TD]
[TD]1Aa[/TD]
[TD]2[/TD]
[TD]$1,000[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]1B[/TD]
[TD]1Ba[/TD]
[TD]1[/TD]
[TD]$2,000[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]1A[/TD]
[TD]1Aa[/TD]
[TD]1[/TD]
[TD]$1,001[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]