creating multidimensional arrays from a table and using indexes to access specific a value

[xcelnic]

-- Sample Data---
[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width: 48pt; text-align: center;"> </colgroup><tbody>[TR]
[TD="width: 64, align: center"][TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Grandparent[/TD]
[TD="width: 64"]Parent[/TD]
[TD="width: 64"]Child[/TD]
[TD="width: 64"]Fee[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ON[/TD]
[TD]A[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]ON[/TD]
[TD]B[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]ON[/TD]
[TD]C[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]UP[/TD]
[TD]D[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]UP[/TD]
[TD]E[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]UP[/TD]
[TD]F[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]XL[/TD]
[TD]G[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]XL[/TD]
[TD]H[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]XL[/TD]
[TD]I[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]FT[/TD]
[TD]J[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]FT[/TD]
[TD]K[/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]FT[/TD]
[TD]L[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]ON[/TD]
[TD]M[/TD]
[TD]65[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]ON[/TD]
[TD]N[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]ON[/TD]
[TD]O[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]WP[/TD]
[TD]P[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]WP[/TD]
[TD]Q[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]WP[/TD]
[TD]R[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]BK[/TD]
[TD]S[/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]BK[/TD]
[TD]T[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]BK[/TD]
[TD]U[/TD]
[TD]105[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]ON[/TD]
[TD]V[/TD]
[TD]110[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: center"][/TD]
[TD="class: xl65, width: 64, align: center"][/TD]
[TD="width: 64, align: center"][/TD]
[/TR]
</tbody>[/TABLE]

The data above shows a family tree. I want to store this data in a suitable data structure "Multidimensional array" and use indexes to access the Fee.

The indexes are generated from user form controls in a different sheet. I want to be able to pass the indexes as arguments to a function and retrieve the fee of a particular child depending on his ancestors i.e grandparent & parent.

How can I store this data into a multidimensional array and access the product price by passing the indexes as arguments to a function?

I have a function that works well for a perfect 3*3 matrix "with the indexes are passed in as arguments" I want to be able to use it even when the child count for the ancestors is not a perfect 3

Function itemprice(a, e, c, d, Optional ByVal z As Integer = 0)
Dim Items(1 To 3, 1 To 3, 1 To 3) As String
Dim Itemz(1 To 3, 1 To 3, 1 To 3) As String
Dim b As Integer, s As Integer, i As Integer, h As Integer
h = 2
        For b = 1 To 3
            For s = 1 To 3
                For i = 1 To 3
                    Items(b, s, i) = Worksheets("index").Cells(h, 8).Offset(0, d).Value
                    Itemz(b, s, i) = Worksheets("index").Cells(h, 8).Offset(0, d).Value + _
                                     Worksheets("index").Cells(h, 8).Offset(0, 1).Value
                    'If Items(b, s, i) = "D" Then itemprice = b & s & i
                    h = h + 1
                Next i
            Next s
        Next b
        If z Then itemprice = Itemz(a, e, c) Else itemprice = Items(a, e, c)
End Function

 

[Kyle123]

A multi-dimensional array isn't really a suitable data structure for hierarchical data. You'd be better off making a tree using collections, that would allow fast access without looping - I suspect that you're trying to solve the wrong problem.

If you want to keep the flattened structure, simply create a collection with a composite key of the parent parts. That allows fast access without looping

 

[Andrew Poulsom]

You can also use a formula:

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Grand parent	Parent	Child	Fee		1	UP	E	25
2	1	ON	A	5
3	1	ON	B	10
4	1	ON	C	15
5	1	UP	D	20
6	1	UP	E	25
7	1	UP	F	30
8	1	XL	G	35
9	1	XL	H	40
10	1	XL	I	45
11	2	FT	J	50
12	2	FT	K	55
13	2	FT	L	60
14	2	ON	M	65
15	2	ON	N	70
16	2	ON	O	75
17	2	WP	P	80
18	2	WP	Q	85
19	2	WP	R	90
20	3	BK	S	95
21	3	BK	T	100
22	3	BK	U	105
23	3	ON	V	110
Sheet1

Cell Formulas
Range	Formula
I1	=SUMIFS(D:D,A:A,F1,B:B,G1,C:C,H1)

 

[xcelnic]

Hi,

Thank you all for your prompt replies

@Andrew I would like to implement this logic in vba since I want to invoke a function that will return a value after a user interacts with form controls on the sheet

@Kyle123 a collection sounds good to me. but i'm kind of stuck, I would highly appreciate if you would guide me on how to populate the collection object

 

[Andrew Poulsom]

You can enter a formula like that in VBA.

 

[Kyle123]

As a simple example:

Sub test()


    Dim c As Collection
    Dim vals, x As Long
    
    Set c = New Collection
    
    
    vals = Sheets(1).Cells(1, 1).CurrentRegion.Value
    For x = 2 To UBound(vals)
        c.Add vals(x, 4), vals(x, 1) & vals(x, 2) & vals(x, 3)
    Next x
    
    Debug.Print c("1XLG")
    
End Sub

 

[xcelnic]

Thank you all, both your formula's work after testing

I am have opted for Andrews Solution using the SUMIFS function in VBA, now I have to tidy things up with my original form.

I must say I've found the best forum ever, @Kyle123 Indeed I was trying to solve the wrong problem

Thank you once more!!