Please help
I have a report I do monthly
I have coded Excel to strip the data and build a report which I wrote years ago and it has worked fine.
the data format has changed and the old code no longer function with the new format so new code is required.
Here is my problem which I have worked on for over a month now;
data sheet has several lines of data as follows;
name, product, type1, amount1, type2, amount2 up to possibly 10 types and possibly 10 products.
type1 may be in colume 3 or 5 or 7 etc. They are mixed up and each line may have several or none
The product is always in colume 2
There could be 100's of pages of this data
bills orchard, preservs, peach, 20, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
bills orchard, preservs, orange, 20, apple, 10, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
bills orchard, jelly, peach, 5, apple, 10, pear, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
Franks orchard, preservs, peach, 20, apple, 10, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
I need to total each of the types by product.
preservs
Apple, total 20
Orange, total 20,
Peach, total 40,
jelly
apple, total 10,
pear, total 2
Peach, total 5,
I have put data in a dictionary using class modules and totaled other amounts that are always in the same colume on every line, say as example fruit in colume 1,
but several products spread out in random columes on same line has me stumped. any help is appreciated.
My example here is very simplified. the actual data has like a 100 columes and consist of many more items I track other than "fruit" in this example. The method when determined can be applied to all the items however. So ... If
I can crack this simplified version I can build the more complicated app I need. Just stumped on how to load a dictionary with "random" columes and varied numbers of product on same line.
Sub readData_dict()
Dim dict As New Dictionary
'Get the range
Dim rg As Range
Set rg = shfruit.Range("B2").CurrentRegion
'read through the data
Dim i As Long
Dim name As String, fruit As clsFruit
For i = 2 To rg.Rows.Count
name = rg.Cells(i, 1).Value
If dict.Exists(name) = False Then
Set fruit = New clsFruit
fruit.name = name
dict.Add key:=fruit.name, Item:=fruit
Else
Set fruit = dict(name)
End If
With fruit
.sales = .sales + rg.Cells(i, 3).Value
.amount = .amount + rg.Cells(i, 4).Value
End With
Next
Call PrintDictionary(dict)
End Sub
I have a report I do monthly
I have coded Excel to strip the data and build a report which I wrote years ago and it has worked fine.
the data format has changed and the old code no longer function with the new format so new code is required.
Here is my problem which I have worked on for over a month now;
data sheet has several lines of data as follows;
name, product, type1, amount1, type2, amount2 up to possibly 10 types and possibly 10 products.
type1 may be in colume 3 or 5 or 7 etc. They are mixed up and each line may have several or none
The product is always in colume 2
There could be 100's of pages of this data
bills orchard, preservs, peach, 20, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
bills orchard, preservs, orange, 20, apple, 10, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
bills orchard, jelly, peach, 5, apple, 10, pear, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
Franks orchard, preservs, peach, 20, apple, 10, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0
I need to total each of the types by product.
preservs
Apple, total 20
Orange, total 20,
Peach, total 40,
jelly
apple, total 10,
pear, total 2
Peach, total 5,
I have put data in a dictionary using class modules and totaled other amounts that are always in the same colume on every line, say as example fruit in colume 1,
but several products spread out in random columes on same line has me stumped. any help is appreciated.
My example here is very simplified. the actual data has like a 100 columes and consist of many more items I track other than "fruit" in this example. The method when determined can be applied to all the items however. So ... If
I can crack this simplified version I can build the more complicated app I need. Just stumped on how to load a dictionary with "random" columes and varied numbers of product on same line.
Sub readData_dict()
Dim dict As New Dictionary
'Get the range
Dim rg As Range
Set rg = shfruit.Range("B2").CurrentRegion
'read through the data
Dim i As Long
Dim name As String, fruit As clsFruit
For i = 2 To rg.Rows.Count
name = rg.Cells(i, 1).Value
If dict.Exists(name) = False Then
Set fruit = New clsFruit
fruit.name = name
dict.Add key:=fruit.name, Item:=fruit
Else
Set fruit = dict(name)
End If
With fruit
.sales = .sales + rg.Cells(i, 3).Value
.amount = .amount + rg.Cells(i, 4).Value
End With
Next
Call PrintDictionary(dict)
End Sub