Help with comparing Variant Array with itself

Hi. I am trying to get this code to work but when I run it to str1, I get Subscript out of range. I need this code to run very efficiently so I used a variant instead of looping through all cells. I have ~ 300k rows and this same operation was difficult for me to do in access.

The code essentially saves a column as variant, and calculates a proportion with 1 / (Number of times that string matches all others) in the next column's cell over. Hope you can see what I am trying to do. Also please disregard any error "cant divide by zero". Thanks in advance!

    Dim compare As Variant
    Dim i As Long
    Dim count As Long
    Dim str1, str2 As String
    
    compare = Sheets("Raw Data Stacked").Range("data[[#data],[Column1]:[Column1]]").Value
        For i = LBound(compare) To UBound(compare)
             For j = LBound(compare) To UBound(compare)
             str1 = Join(compare(i), "")
             str2 = Join(compare(j), "")
        
             If str1 = str2 Then
                   count = count + 1
             End If
             Next j
        Cells(i + 1, 21) = 1 / count
        count = 0
    Next i

I think you are using the join function incorrectly. It looks like you are using it like the CONCATENATE() function where you are expecting it to join two strings. According the help file (http://msdn.microsoft.com/en-us/libr...=vs.90%29.aspx ), the Join function takes a one dimensional array and joins each element of the array, divided by the assigned delimiter. In observing the "compare" variable in the Locals/Watch window, it appears that VBA is treating compare as a 2D array (even though one dimension is 1 to 1), and not accepting compare as an argument for the join function. I'm not exactly sure what you are doing with the join function (I don't see why you are trying to join the array into a single long text string). If you want to use the join function, you are going to need to do something different in assigning the array to compare.

One observation. It seems like all this is doing is a relatively simple COUNTIF(). U2=1/COUNTIF($A$1:$A$300000,A1) (or whatever the reference is). Depending on where this task fits into the overall project, it might be easier to use a worksheet function rather than VBA.

Why can you not test compare(i)=compare(j) [[or compare(i,1)=compare(j,1) as 2D arrays]]? This is not the kind of VBA programming I do, so I'm not very good at it, but I have never heard that you cannot test two variants for "exactly equal to". I have heard of other issues with testing for "exactly equal to" (round off error and such), but not that variants cannot inherently be so tested. It would appear to me that, with the recognition that compare is actually a 2D array, it should be fairly straightforward to test for compare(j,1)=compare(i,1).

Using variants can be one trick to speed some of these things up sometimes. In some ways, though, the hard reality of what you are doing (as I understand it so far) is that your current algorithm is asking the computer (whether using VBA or using the COUNTIF() worksheet function) to perform 90 trillion (300000*300000) operations, and that is just to test compare(i)=compare(j). That does not include incrementing loop counters, retrieving data from the spreadsheet or putting data in the spreadsheet and so on.

I suspect that, if VBA is really going to be useful for speeding this operation up, you will need a better algorithm -- not some "trick" based on variable data type. For example: could you sort the input data? If the input data were sorted, you would not need to test every entry. You would only need to count the number "A"s in a localized block until you come to an "AB" entry, then you can count the AB entries.

As I noted, this is not the kind of programming I do, so there are certainly others here may be better at developing this type of algorithm. I think you will need to research some other ideas to really see any gains in computation time.

Can you describe the contents of the data?

Ae you just trying to generate a histogram of the data?

So the result in the Proportion column is the goal of the calculation?

How is it calculated? Just the reciprocal of the count of Name?

Is this a VBA version of COUNTIF?

If you sort by name, the calculation could be done much faster by limiting the range reference to a small fraction of the columns. Or you could read the name column into an array and use a dictionary.

BTW, there is no issue with comparing variants to one another.

How many unique entries are there in column A? One thing I observe is that G3 is repeating the exact same calculation done in G2. If there were enough duplication like this, some form of "I've already counted 'Bob', so i don't need to do it again." Could improve computation time. Maybe build a second list with "unique" names from column A, then use the COUNTIF() or even FREQUENCY() functions.

Or, as I suggested earlier, if the data were sorted on "name" so that you could be certain that all of Bob's entries will be together in the list, then you should be able to limit your count range to the immediate area around Bob's entries, rather than needing to count across the entire 3E5 rows in the database.

Right. The companion part after sorting is using a COUNTIF formula starting in the first row that only looks down far enough to catch the max number of duplicates.