Populating 2-D array w/ Application.[Function] results

C

CompleteNewb

I have a sheet that has multiple occurrences of values in Column A. I
already have made an array that has the UNIQUE values: Array1.

I now need to make an Array2 that has the items from Array1 as its 1st
column, and the number of times each item appears in Column A in its second
column. You can probably see what I'm trying to do by looking at this:

For i = 0 To UBound(Array1)
ReDim Preserve Array2(0 To i, 0 To y)
Array2(i, y) = (Array1(i),
Application.CountIf(Sheets("SheetName").Range("A1:A39"), Array1(i))))
i = i + 1
Next i

Or, I could even just ADD the Count as a new "column" or "dimension" to the
existing Array1, either way is fine with me. I just can't seem to figure out
the right syntax for populating the 2nd part of the array, or making a new
array that has the elements from the 1st array and the count of times that
element appears in Column A.

I feel embarrassed at having to ask such a simple question; the truth is all
the stuff about populating arrays that I could find was either for a 1-D
(which I knew already), or things MORE complex than what I'm trying to do; I
couldn't find the sweet spot.

I appreciate any help, and thanks for reading.
 
B

Bob Phillips

Add some code to insert a column after the column of unique values, and add
a simple SUMIF formula. Then just load the array wholesale from those 2
columns.


HTH

Bob

"CompleteNewb" wrote in message
I have a sheet that has multiple occurrences of values in Column A. I
already have made an array that has the UNIQUE values: Array1.

I now need to make an Array2 that has the items from Array1 as its 1st
column, and the number of times each item appears in Column A in its second
column. You can probably see what I'm trying to do by looking at this:

For i = 0 To UBound(Array1)
ReDim Preserve Array2(0 To i, 0 To y)
Array2(i, y) = (Array1(i),
Application.CountIf(Sheets("SheetName").Range("A1:A39"), Array1(i))))
i = i + 1
Next i

Or, I could even just ADD the Count as a new "column" or "dimension" to the
existing Array1, either way is fine with me. I just can't seem to figure out
the right syntax for populating the 2nd part of the array, or making a new
array that has the elements from the 1st array and the count of times that
element appears in Column A.

I feel embarrassed at having to ask such a simple question; the truth is all
the stuff about populating arrays that I could find was either for a 1-D
(which I knew already), or things MORE complex than what I'm trying to do; I
couldn't find the sweet spot.

I appreciate any help, and thanks for reading.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top