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
My Goal
I would like to re-order this entire dataset based on the following:
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!
<tbody>
</tbody>
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!
Column A | B | C | D | E |
Unique ID | Category 1 | Category 2 | Ranking of $ value in Category 2 | $ value |
1000 | 1A | 1Aa | 2 | $1,000 |
1001 | 1B | 1Ba | 1 | $2,000 |
1002 | 1A | 1Aa | 1 | $1,001 |
<tbody>
</tbody>