Need to count unique values in a sorted list

[mrwilliams]

I have an excal table with about 85,000 lines. I need to match on one column and once the match is true, I need to count all the unique values in a second column without counting duplicates. Column "Prod Tab" is the column I need to match and column "SubID" are the values that I need unique counts on.

Example follows:

SubID Prod Tab
824510 9
601632 9
601632 9
601632 9
601632 9
601632 9
601632 9
828280 9
616411 9
616411 9
616411 9
616411 9
616411 9
830160 9
800676 9
800676 9
800676 9
800676 9
800676 9
800676 9

Prod Tab can be a value from 0 to 13. In this example I am matching on "9" in the column and those rows mathced I want to determine the number of unique values in the SubID column. It this example the end result would be "6".

I can do this with a piviot table, but was hoping to accomplish it with VBA and report the results of each such test to a spefice range.

Can some sheer me to a solution?

Mr G Williams

 

[mrwilliams]

Quote:

"just curious - why do you need to apply either my or Aladin's formula to the entire range of 80,000 rows?

as you said, you only have 13 numbers in the second column. cant you just do a one-time calculation (so have numbers from 1 to 13 in say, col, J1:J13), and next to it (K1:K13) the corresponding counts for those 13 using either formulas."

The reason is that I have about 20 columns of data x 85,000 rows. The rest of the columns have information that must be associated with the row. All the data feeds a large report generator that currently works properly.

Each of the 85,000 rows is assigned a Prod Tab or Product Tab which represents a family of products. That is the "9" value in my example. Each family of products is a collection of individual parts that make up one complete product as sold to a subscriber thus SubID. I need to count the subID's as one and total all the other information for that product tab.

It is a list within a list if you well. I have to kepp all the 9's together and then count the subID's that are unique that also have a prod tab of 7, 8, 9,10 and so on. That tell me how many total sales were made. I then count all the rest of the data in ech column for the total sales elements of tabs 7, 8, 9, 10 and so on. To break the list up causes problems when I need those totals.

Piviot tables can do that work, but I don't want to manage that many tables. I was hoping I could read the data into arrays and use functions on the array's.

Mr Gw

 

[sulakvea]

i hear you - but what i meant was you can have a separate column somewhere, and instead of copying an array formula down (80,000 rows) and crashing excel, you could create a vlookup off of that 13-line long table, and copy down. i'm sure excel can handle 80,000 vlookups fairly easily

 

[ZVI]

VBA solution seems more suitable for such huge range:

' Returns the amount of unique values in UniqCol range with CondValue matched in CondCol range
' Example: =UniqCondCount(A:A;B:B;9)
Function UniqCondCount(UniqCol As Range, CondCol As Range, CondValue) As Double
  Dim a, b, v, r&
  a = Intersect(UniqCol.Parent.UsedRange, UniqCol).Value
  b = Intersect(CondCol.Parent.UsedRange, CondCol).Value
  With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For Each v In b
      r = r + 1
      If v = CondValue Then
        .Item(CStr(a(r, 1))) = 0
      End If
    Next
    UniqCondCount = .Count
  End With
End Function

Regards,
Vladimir

 

[Aladin Akyurek]

An alternative, faster formula, if you install the morefunc.xll add-in...

Control+shift+enter, not just enter:

=COUNTDIFF(IF($B$2:$B$21=E2,$A$2:$A$21),,{FALSE;""})

If the data can be sorted in ascending order, we can set up more efficient versions of the COUNTDIFF formula as well as the FREQUENCY formula.

The following link some idea on performance:

http://www.mrexcel.com/forum/showthread.php?t=292473

 

[mrwilliams]

Thanks to all, I have very good solutions, one in VB code and the other as a formula. They both work and give me the counts I needed.

You guys rock!

GW