VBA Sorting & Summing
In this template, we have spreadsheet with 9 columns. The spreadsheet in sheet includes products that purchased from vendors , product unit prices and total amounts.The vendors are listed in column B and the purchased product amounts in column I based on dates.
We will follow 2 different ways for two sheets that contained same spreadsheet to find the amount of purchased product from a vendor (process of summing unique values).
Solution 1 (for Example1 sheet ) With the sum_unique_items procedure firstly, vendor names in column B are listed as unique values in column K using VBA AdvancedFilter method.
To find the total amount for each individual value(vendor) in column K, the SUMIF formula is entered using a loop into the cells in column L corresponding to the value .
Solution 2 (for Example2 sheet ) The range A: I is sorted alphabetically as ascending based on Column B.
Subtotals are created for the used range on the Example2 sheet with the VBA Subtotal method. The table is grouped by the second field and subtotals are added to the field 9 (Column I).
They are colored using a loop to highlight cells that contained subtotals :