how to load data in dictionary from random columns?

l_eonandr

New Member
Joined
Feb 18, 2016
Messages
25
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Forum statistics

Threads
1,223,885
Messages
6,175,187
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top