get unique/distinct counts for pivot table

Yeah, same thing, basically.  That's what I'm doing and it's seriously taking forever to calculate.


        .Range("k2").Formula = "=sum(1/COUNTIF($C$2:$C$" & lastRow & ",C2),0)"
        DelCount.FillDown

        .Range("k2").Formula = "=sum(1/COUNTIF($C$2:$C$" & lastRow & ",C2),0)"
        DelCount.FillDown

                                              

1:
2:

Not sure if this is what you mean but if you want to get a unique list from a column of data without draging down 40K rows of formulas you can simply do it using the Advance Filter which would be much quicker...to do this:

1. Go to Data then Advance Filter
2. Check "Copy to Another Location"
3. Highlight the column of data with a header cell included (header cell = title of data below...ie "SALES") 4. For the "Copy to" highlight a range where you want the results to go...be sure this area is large enough to hold the results...keep same length as original data to be sure
5. Click the "Unique Records Only" radial dial
6. Click OK.  

This will paste a copy of ONLY the unique records from the original column of data into the new location.  This can also be done programmatically in VBA.

Adam,
Good to know, but I don't think that's going to help a lot.

Basically, my data dump sheet has tens of thousands of order lines.  The Pivot counter will return the number of lines per order, but I also need to return the number of orders.  That's where I need to do a distinct count and have the ability to use it in my Pivot.

Sorry to say I'm not a big fan of Pivot tables so don't use them, and am not sure what the "Pivot counter" is.  However, if you're trying to do something outside of the Pivot that references the results I'll be glad to look at your Excel file or an example of it to see what can be done.

"Basically, my data dump sheet has tens of thousands of order lines.  The Pivot counter will return the number of lines per order, but I also need to return the number of orders. That's where I need to do a distinct count and have the ability to use it in my Pivot."

Why not do a COUNT on the "Order Number" field.  It will give you a count of the line items per order and a grand total of all orders.  Am I missing something?  Is there not an order number associated with the lines?

See attached file for example.

@adamchicago:  I just meant the PivotTable Count function
@ProdOps:  you're still just counting order lines.  I need to count the number of orders, as well.

Thanks ProdOps...I'm not a Pivot user, but that is a much simpler for the B2 formula.

I guess Rory and I had similar thoughts....   I was working on this just came back to the post with an alternate solution and saw Rory's comment.  You can use the following formula to generate a unique number that you can filter on in the PT.  I am sure there are other solutions but look at the attached file and see if this helps...

=SUMPRODUCT( --(A$2:A2=A2))