I've attached two images that will help visualize what I'm trying to do. I have 4 different types of data that need to be sorted. In the 'example' image, you can see that the accounts (Col A) is all clumped together. The 'output' image shows what I would like to happen. The number of data elements will change for each spreadsheet (macro) I would run the code on. Highlighting of the two sums preferred.
The first section in 'output' is for the accounts that are only numbers. The second sections is for accounts that have letters after the first 3 digits.
A Sum is produced for a combined total of the values in the Amt Column.
Next, when the first three digits are 'Fnd'. Total is produced for this section. The numbers to the right of the Amt column is removed.
Lastly, if the Name Column is named 'Enc' the row would be placed in this section. An alternate way to code this is to evaluate whether the 5th and 6th digit is 0 if it is, the row transfers into the last section and a sum for this section is produced. The numbers to the right of the Amt column is removed.
Note, each section is sorted by the Account without any sorting into any other section.
Example:
After Macro Output:
The first section in 'output' is for the accounts that are only numbers. The second sections is for accounts that have letters after the first 3 digits.
A Sum is produced for a combined total of the values in the Amt Column.
Next, when the first three digits are 'Fnd'. Total is produced for this section. The numbers to the right of the Amt column is removed.
Lastly, if the Name Column is named 'Enc' the row would be placed in this section. An alternate way to code this is to evaluate whether the 5th and 6th digit is 0 if it is, the row transfers into the last section and a sum for this section is produced. The numbers to the right of the Amt column is removed.
Note, each section is sorted by the Account without any sorting into any other section.
Example:
Book1 | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | F | G | H | ||||||
1 | Acct | Name | Amt | New Amt | Difference | |||||
2 | 456 456 | 9 | 5 | 4 | ||||||
3 | 654 2F2 | 8 | 4 | 4 | ||||||
4 | 123 456 | 7 | 3 | 4 | ||||||
5 | Fnd 203 | 6 | 2 | 4 | ||||||
6 | 789 987 | 5 | 0 | 5 | ||||||
7 | 123 001 | Enc | 4 | 1 | 3 | |||||
8 | 654 456 | 3 | 6 | -3 | ||||||
9 | Fnd 101 | 2 | 7 | -5 | ||||||
10 | 258 753 | 0 | 8 | -8 | ||||||
11 | 789 002 | Enc | 1 | 9 | -8 | |||||
Sheet1 |
After Macro Output: