I have a large Excel Table.  I am working in xl2007 but solution must work in 2003.  I want to identify the records where a "document_number" relates to more than one "Service".
My current formula is based on sumproduct which, with 60,000 rows, not suprisingly takes too long.  Is there a better solution?  I am adding to the bottom of the table each month and the values in "Service" may change for all records.

I can't post the precise formula as the vba that applies it is still doing its work :(  However it is of the form:

=sumproduct(--(document_number=Thisdocument_number),--(Service<>=ThisService))>0

All ideas welcomed!

Gavin

hi,

Why not use MS Query?
 

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

Probably just because I haven't used query successfully much tbh.  I'll give that a try - it could have other benefits with this report too.

Maybe getting ahead of myself but I can use the result set from that as the source for a pivot can't I?

 

Gavin

Yes, because it's just a table.  However, it is already a summary of your original table, which is what a PT is.

You could take the SQL back one step and return the data for this...
Then a PT of the resultset might make sense.
 

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

And why could you not pivot on the source data and eliminate the query step?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

Thanks Skip - sound like something to get my teeth into on Tuesday when I am next in the office.  I'll try not to come back with too many questions but any pointers to examples/good sites might speed me on my way.

Thanks again.

Gavin

Gavin

In fact, you can SORT the document_number PT field DESCENDING by the Count of document_number, and you get a pareto of occurrences.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}