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. |
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. |
All times are GMT +1. The time now is 03:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com