Sumproduct or Something faster/better 1

[Gavona]

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

 

[SkipVought]

hi,

Why not use MS Query?

select document_number, count(*)
From
(
select DISTINCT document_number, Service
from [YourSheetName$]
)
group by document_number
having count(*) > 1

Skip,

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

 

[Gavona]

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

 

[SkipVought]

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...

select DISTINCT document_number, Service
from [YourSheetName$]

Then a PT of the resultset might make sense.


Skip,

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

 

[SkipVought]

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!}

 

[Gavona]

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

 

[SkipVought]

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!}