Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating 2-D array w/ Application.[Function] results
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating 2-D array w/ Application.[Function] results
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Alternative to Application.Caller in array functions to avoid wrong results? | Excel Programming | |||
Passing result of multiple split function results to 1 Array | Excel Programming | |||
PLEAS HELP! Newby Needs help Populating Cell w/ Application.usern | Excel Discussion (Misc queries) | |||
Wanting to write results to array instead of sheet, results overwriting.... | Excel Programming | |||
populating one list/combobox based on the results of another | Excel Programming |