Summarize sales data

[Max Bialystock]

I have in columns a & b imported data such as the following:
Book Labels Hummingbird Secret Garden Series 2
Book Labels Il Gatto Secret Garden Series 1
Book Labels Red Wren Secret Garden Series 1
Decoration Christmas Dove Red 1
Ham Bag Carlas 1
Mug Medina Casablanca Series 1
Mug Midnight Peacock Series 1
Tea Towel Spring Chickens Meadows Series 1
Tea Towel Marrakech Signature Series 1


Column A has the item name
Column B has the number sold

How can I get the totals for Book Labels, Decoration, Mugs etc?

 

[Guest]

Max,

It is probably best to split the column a and b into two columns say b and c
where b is the product group and b is the product. This can be done my having
a product group list on another worksheet and running a simple macro to split
the columns:

I called the product group sheet products and it was in the same workbook
as the sales lines.

With the sales lines worksheet open run the following macro. Not there are
no headings.

After you have run it make a pivot table report or use the products with a
sumif.

'---------------------------------------------
Option Explicit

Sub split()

Dim line As Range
Dim pg As Range

Dim foundit As Boolean

ActiveSheet.Columns("b:c").Insert ' add 2 columns
For Each line In ActiveSheet.Range("A1").CurrentRegion
foundit = False
' search products
For Each pg In ActiveWorkbook.Worksheets("products") _
.Range("A1").CurrentRegion
If InStr(1, line.Value, pg.Value, vbTextCompare) = 1 Then
'insert values
line.Offset(0, 1) = pg.Value
line.Offset(0, 2) = Trim(Mid(line.Value, Len(pg.Value) _
+ 1, Len(line.Value)))
foundit = True
Exit For
End If
Next pg
If Not foundit Then ' didn't find it
line.Offset(0, 1) = "Unknown"
line.Offset(0, 2) = line.Value
End If
Next line
Set pg = Nothing
Set line = Nothing
End Sub
'------------------------------------------------------------