Consolidate data in an 2-dimension array in VBA

Hi there,

Can anyone show me a quick way to consolidate data in a 2-dimension array in VBA. I have got an array in VBA with multiple appearance of some identifiers. The array looks like...

"A", 1, 2
"B", 2, 3
"A", 3, 4
"C", 4, 5

I would like a function to consolidate the data and return an array with data like

"A", 4, 5
"B", 2, 3
"C", 4, 5

In the consolidated array, the identifiers will have only one appearance.

The array only exists in memory within a procedure. So I can't use Consolidate method which only works on range.

Thanks,

Huyeote

I have no doubt there are much more elegant ways to do this but I'm just
not that smart so try:

Sub consArray()

'snip
'assumes you already have an array arr
'and that arr is populated.
'results will be in newArr

Dim newArr() As Variant
Dim i As Integer
Dim k As Integer
Dim ident As String
Dim chkArr() As Variant

ReDim chkArr(UBound(arr))

For i = 0 To UBound(arr)
If chkArr(i) = Empty Then
chkArr(i) = "Copy"
ident = arr(i, 0)
For k = 0 To UBound(arr)
If k <> i Then
If arr(k, 0) = ident Then
arr(i, 1) = arr(i, 1) + arr(k, 1)
arr(i, 2) = arr(i, 2) + arr(k, 2)
chkArr(k) = "Del"
End If
End If
Next k
End If
Next i

k = -1
For i = 0 To UBound(chkArr)
If chkArr(i) = "Copy" Then k = k + 1
Next i
ReDim newArr(k, 2)
k = 0
For i = 0 To UBound(chkArr)
If chkArr(i) = "Copy" Then
newArr(k, 0) = arr(i, 0)
newArr(k, 1) = arr(i, 1)
newArr(k, 2) = arr(i, 2)
k = k + 1
End If
Next i

'do something with newArr
End Sub

Hope this helps
Rowan

Huyeote wrote:
> Hi there,
>
> Can anyone show me a quick way to consolidate data in a 2-dimension
> array in VBA. I have got an array in VBA with multiple appearance of
> some identifiers. The array looks like...
>
> "A", 1, 2
> "B", 2, 3
> "A", 3, 4
> "C", 4, 5
>
> I would like a function to consolidate the data and return an array
> with data like
>
> "A", 4, 5
> "B", 2, 3
> "C", 4, 5
>
> In the consolidated array, the identifiers will have only one
> appearance.
>
> The array only exists in memory within a procedure. So I can't use
> Consolidate method which only works on range.
>
> Thanks,
>
> Huyeote
>
>