• SHARE

# VBA Sorting & Summing Unique Values

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.
`Columns("B:B").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Columns( "B:B"), CopyToRange:=Range("'Example1'!K1"), Unique:=True `

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 .
`For i = 2 To Cells(Rows.Count, 11).End(xlUp).Row Cells(i, "L").FormulaR1C1 = "=SUMIF(C[-10],RC[-1],C[-3])"Next i`

Result :

Solution 2 (for Example2 sheet ) The range A: I is sorted alphabetically as ascending based on Column B.
`Range("A2:I" & Cells(Rows.Count, 1).End(xlUp).Row).Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom`

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 :
`For Each rng In Range("I2:I" & Cells(Rows.Count, 9).End(xlUp).Row) If rng.HasFormula Then With rng .Interior.ColorIndex = 37 .Font.Bold = True End With End If Next `

Result :

📥 Download sample workbook

• 1

• 2

• 3

• 4

• 5