Unique Worksheet Formula to VBA conversion

[BAlGaInTl]

Hello all... it's been a while since I've been on here.

I have a formula in a column that checks to see if a value in another column is unique. It puts 1 if it's the first time it's seen it, and 0 if it isn't unique. Here is the formula:

=IF(COUNTIFS($A$2:$A2,A2)=1,1,0)

That same logic is also used in other formulas to do some special math to make sure that I only count things once. Normally I could accomplish this with my pivots, but due to the nature of what we are doing, the data model method doesn't work. We also use grouping, and I can't for the life of me understand why you can't do a data model AND grouping.

So basically, I'm trying to automate the tedious task of gathering the data and then adding a bunch of formulas to calculate the fields that we need. here's a snippet of code that performs the above:

With wsMetrics
    .Cells(1, i) = "UniqSamp"
    .Cells(2, i).FormulaR1C1 = "=IF(COUNTIFS(R2C" & iSampIDCol & ":RC" & iSampIDCol & ",RC[" & iSampIDCol - i & "])=1,1,0)"
    .Cells(2, i).AutoFill Destination:=Range(.Cells(2, i), .Cells(iLastRow, i))
    .UsedRange.Value = .UsedRange.Value
End With

Where:

iSampIDCol = Column that I'm testing
i = Column where the R1C1 formula is going

There are about 8 separate calculations that I do. Some of them are more complex, but may still have that formula embeded in them. The above technically works just fine, but a month of data can be about 15K rows. So I've found that it's painfully slow from an automation standpoint.

I've tried to turn of automatic calculations and do the autofill on the last 8 calculated columns all at once, but it doesn't seem to make much of a difference.

I thought that there must be a more efficient way to get this effect in VBA.

Thoughts?

Thanks in advance.

 

[Fluff]

For that one example you could use something like

Sub GetUnique()
   Dim ary As Variant
   Dim i As Long, j As Long, k As Long
   
   ary = Range("A1").CurrentRegion.Value2
   [COLOR=#ff0000]j = 3: k = 9[/COLOR]
   With CreateObject("scripting.dictionary")
      For i = 2 To UBound(ary)
         If Not .Exists(ary(i, k)) Then
            .Add ary(i, k), Nothing
            ary(i, j) = 1
         End If
      Next i
   End With
   Cells(1, j).Resize(UBound(ary)).Value = Application.Index(ary, , j)
End Sub

 

[BAlGaInTl]

Thanks, that definitely works, and I can adapt it.

Now I just need to wrap my head around what it's actually doing.

What is the purpose of "k"? Why is it set = 9?

 

[Fluff]

Sorry should have mentioned
j is the output column (your i variable) & k is the test column (your iSampIDCol).
change the values in red (shown in post#2) to suit

I simply set them to an arbitrary figure.

 

[BAlGaInTl]

Sorry should have mentioned
j is the output column (your i variable) & k is the test column (your iSampIDCol).
change the values in red (shown in post#2) to suit

I simply set them to an arbitrary figure.

Looks like I spoke too son....

When I set j to my output (22) and k to my test column (1)

I get a subscript out of range error on this line:

ary(n, j) = 1

 

[Fluff]

Glad to help & thanks for the feedback

 

[BAlGaInTl]

Glad to help & thanks for the feedback

See above...

Looks like I spoke too soon. It was working (coincidentally) with the values you had. But when I changed my output column (22) and test column (1), I get the above error.

Note, I changed i to n since I was already using i.

 

[BAlGaInTl]

See above...

Looks like I spoke too soon. It was working (coincidentally) with the values you had. But when I changed my output column (22) and test column (1), I get the above error.

Note, I changed i to n since I was already using i.

Ah... nevermind. The error is with "CurrentRegion" I think. Column 22 is the first column that is outside of my original data. At least I think that's the issue.

 

[Fluff]

If col V is totally blank try

   Ary = Range("A1").CurrentRegion[COLOR=#ff0000].Resize(, 22)[/COLOR].Value2

 

[BAlGaInTl]

If col V is totally blank try

   Ary = Range("A1").CurrentRegion[COLOR=#ff0000].Resize(, 22)[/COLOR].Value2

I'll try that.

I was able to fix it by defining the arrayy with my own variables too.

Thanks again. This is fantastic.

I'm really going to have to study up on arrays a bit more. It's really a weak point for me.