XLS Array - Unique Value and Frequency?

I am stumbling to produce a result on this one.

I have a collection of XLS Cells (15 x 10K). I'd like to determine the total UNIQUE values appearing within the array, and then the frequency with which each appears? I do not know the possible unique values in advance.

Simple Example (3 x 4):

A | B | C
B | B | A
C | C | C
D | A | B

Result:

A: 3
B: 4
C: 4
D: 1

Any suggestions to approach this problem?

Assuming the data starts from A1

Sub test()
    Dim a, e, x
    With Range("a1").CurrentRegion
        a = .Value
        With CreateObject("Scripting.Dictionary")
            .CompareMode = 1
            For Each e In a
                If e <> "" Then .Item(e) = .Item(e) + 1
            Next
            x = Application.Transpose(Array(.keys, .items))
        End With
        With .Offset(, .Columns.Count + 1).Resize(UBound(x), 2)
            .CurrentRegion.ClearContents
            .Value = x
        End With
    End With
End Sub

Wow, that works very nicely!

Did you have that sitting around waiting, or knocked off on the fly?


Thanks!
Mark

Glad you like it.

This is an easy task and I did it on the fly.

Excellent.

Which parameter should I tweak to bound the start of the array to: C6?

Thanks again!

Just replace a1 with the address that is within a data range.

CurrentRegion will catch the block of the range that includes its address.

Hmmm. Yes, I did adjust "a1" value to "c6", but the routine still captured unwanted header rows and columns.

Like this, I assume?

Sub test()
    Dim a, e, x
    With Range("c6").CurrentRegion
        a = .Value
        With CreateObject("Scripting.Dictionary")
            .CompareMode = 1
            For Each e In a
                If e <> "" Then .Item(e) = .Item(e) + 1
            Next
            x = Application.Transpose(Array(.keys, .items))
        End With
        With .Offset(, .Columns.Count + 1).Resize(UBound(x), 2)
            .CurrentRegion.ClearContents
            .Value = x
        End With
    End With
End Sub

If you have header row and want to exclude it,

change

a = .Value

to

a = .Offset(1).Value

Is this what you want?

Originally Posted by jindon

Is this what you want?

Yes, partially.

That offset ignores the first XX rows. I would also like to ignore the first YY columns.

Is that possible?


Thanks for you patience

Yes
Something like

.Offset(1, 2).Value

Success!

Kind thanks

Regarding offset by column.

It will affect the result when it runs after the first run, so to be exact, it should be

.Offset(xx, yy).,Resize(.Rows.Count - xx, .Columns.Count - yy)

And this will avoid the loop the row(s)/columns(2) that doesn't need to.