Extracting Specific dimensions/values from multidimensional array

  • Thread starter Thread starter junkabl
  • Start date Start date
J

junkabl

Say I have sales data
ie 5 sales, on day 1, in year one, in currency x of a code "a".

what i want to do is be able to do is to convert all the currencies to
one currency and display the data by different codes.

i thought about using a 4 dimensional array, ie(code,ccy,year,day) and
inserting individual sales for each of the data points.

Day
code ccy Year 1 2
a x 1 5 20
a x 2 10
a y 1 5 20
a y 2 10
b x 1 5 20
b x 2 10
b y 1 5 20
b y 2 10

Once i have inserted this data into such an array, how do i extract
say all of the data for just code a for example?
in practice the number or currency for each code will be different,
and the number of codes will be different, but the number of years and
days will be the same for each code-ccy combination

(Context - Actuarial - this is a runoff/delay triangle)

Any help most appreciated!
 
how do you mean i dont understand?

i dont know how to attach a file (it is rather large) on here. and the
data is already in a worksheet?

What I am looking for is methods how to extract certain elements, or
whole sub groups from an array, ie

x(1 To 2, 1 To 2, 1 To 2, 1 To 2)

so for example

Sub test()
Dim x As Variant

ReDim x(1 To 2, 1 To 2, 1 To 2, 1 To 2)
For i = 1 To 2
For j = 1 To 2
For k = 1 To 2
For l = 1 To 2

x(i, j, k, l) = 1

Next l
Next k
Next j
Next i

End Sub

how do i get what is in array x, back into a worksheet? but only for
say i=1 and j=2?
 
If you data is already on the WS, why not use Excel's features/function like
Filter, Sort, Count, Sum, VLOOKUP etc to find your result.
Arrays are fast, but I think will find it complex working with 4-D arrays.
Possibly using customs Types, you can simplify it, but the worksheet
approach would seem simpler.

NickHK
 
Its not really practical to do so, as I am working with 65000 by 50
cells, and calculations need to be made on the fly, ie adjusting for
currencies.

what im really looking for, is the correct method or syntax, to
extract particular dimensions from the array

any help most appreciated.
 
Back
Top