Identify and Sum Like Array elemets

[DMS]

All,

I am trying to come up with a way to identify and then sum like Elements in
an Array.

For Example, Lets Say I have an array that contains the following items
1.First Name
2.Last Name
3.Hours worked

The Macro I am witing is adding this type of data to an Array, but I don't
want there to be duplicate array items for the same First and Last Name.

So lets say the first item in my Array is John, Smith, 20. Before I add the
next item to the Array, I want to make sure that the First and Last name
aren't already in the Array, if it is already in the Array, then it should
add the hours to the existing Array item.

Or, is there a way to sum like elements after an Array is created?

Thanks for your help.

DMS

 

[JackD]

There are multiple ways of doing this. This is a quick and dirty way of
doing something like this which I put together a few years ago. I think it
works.
I know a bit more now and I imagine you could get more elegant and halt the
search after the value is found to exist in the array, but the calculate
pretty fast so I haven't found any issues with it.

---snip-------

Private Sub MyArray()
Dim myTask As Task
Dim myTasks As Tasks
Dim Addme As Boolean
Dim myList As String

Set myTasks = ActiveProject.Tasks
ArrayIndex = 0
ReDim Preserve WorkType(ArrayIndex)
For Each myTask In myTasks
Addme = False
If Not myTask Is Nothing Then
If Not myTask.summary Then
If myTask.Text20 <> "" Then
Addme = True
For ArrayIndex = 0 To UBound(WorkType)
If myTask.Text20 = WorkType(ArrayIndex) Then
Addme = False
End If
Next ArrayIndex
End If
If Addme = True And myTask.Text20 <> "" Then
WorkType(ArrayIndex - 1) = myTask.Text20
ReDim Preserve WorkType(ArrayIndex)
End If
End If
End If
Next myTask
End Sub

----------snip-----
You will need to increase the number of dimensions of the array to 2 and use
some concatenation for your test

fullname = myArray(i,0) & myArray(i,1)

Remember that arrays are zero-based by default.

 

[John]

All,

I am trying to come up with a way to identify and then sum like Elements in
an Array.

For Example, Lets Say I have an array that contains the following items
1.First Name
2.Last Name
3.Hours worked

The Macro I am witing is adding this type of data to an Array, but I don't
want there to be duplicate array items for the same First and Last Name.

So lets say the first item in my Array is John, Smith, 20. Before I add the
next item to the Array, I want to make sure that the First and Last name
aren't already in the Array, if it is already in the Array, then it should
add the hours to the existing Array item.

Or, is there a way to sum like elements after an Array is created?

Thanks for your help.

DMS

DMS,
There are probably lots of ways to do what you need. First of all I
assume you are using a single element array that is indexed. For
example, I would use the array:
RData(i) where "i" is the index for the full name and the array contains
the "hours" value. It could also be a two element array if for some
reason you need to keep first and last names separately (don't know why
but you might).

Depending on how you gather the data, you may or may not need to use a
compare loop to check for repeat names. For example, if you are looping
through Project's resources, each name is listed only once and all you
ned to do is to gather up the values. On the other hand, if you are
looping through tasks in a project and want to examine each task
resource assignment, then a simple compare loop is appropriate as each
task and its resource assignments are read. For something like that, I
have used the following type of compare structure to identify if the
resource being queried is already in the array (obviously this code is
part of a larger macro).

ResTracker:
Dim NewRes As Boolean
NewRes = True
If FirstRes = True Then
ResNam(Res) = a.ResourceName
FirstRes = False
Else
For i = 1 To RMax
If a.ResourceName Like ResNam(i) Then
NewRes = False
Res = i
Exit For
End If
Next
If NewRes = True Then
Res = RMax + 1
RMax = Res
ResNam(Res) = a.ResourceName
End If
End If
Return

Hope this helps.
John
Project MVP