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

Please Login or Register  to view this content.

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?

Please Login or Register  to view this content.

If you have header row and want to exclude it,

change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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