Re: Identifying and counting duplicates

if you use client id as a row field and as a data field set to count and
use a page field (Problem) to filter on crashes, then the number of rows the
table produces is the number of clients who have had crashes. This could be
gotten from the pivot table with =CountA(A4:A500) or similar.

--
Regards,
Tom Ogilvy

"ngarutoa" <whakaaro@xxxxxxxxx> wrote in message
news:9599c5291c298c7bcdbc05f045a@xxxxxxxxxxxxxxxxxxxxx
> Thanks again to both of you
>
> That gets the data into my pivot table. But may have not explained myself
> properly re what I expect to report on. Pivot tables don't tell me how
many
> unique people had crashes just the number of crashes. For example.
>
> Date | Time | ID | Division | Consultant | Client ID | Problem |Solution
>
> Client A345 had a crash which was fixed on 1/1/2005
> Client A345 had a crash which was fixed on 15/2/2005.
> Client ??? had a bug which was referred on 3/3/2005
> Client ??? had a crash which was referred on 5/3/2005
> client 7890 had a bug which was fixed on 6/3/2005
>
> The report would show 2 Clients have had crashes not 3.
>
> Again thanks for the help
> ngautoa
>
>
> > If the imported data is placed in the same location, you can use a
> > dynamically defined named range
> >
> > Name=List
> > RefersTo= =Offset(Sheet1!$A$1,0,0,CountA(Sheet1!$A:$A),10)
> > where list is then used as the source of your pivot table.
> >
> > As an example.
> >
> > "ngarutoa" <whakaaro@xxxxxxxxx> wrote in message
> > news:9599c5291bf88c7bcc8e87bc2b0@xxxxxxxxxxxxxxxxxxxxx
> >> Hello Tom,
> >>
> >> Thanks for that
> >>
> >> I can use pivot tables but was hoping that there was a way that I
> >> could
> >>
> > use
> >
> >> code because will be using a *** where the data will be imported
> >> and so the range will be dynamic not static. Will be utilising pivot
> >> tables in
> >>
> > the
> >
> >> short term. Until I can figure a way to get the queries working.
> >>
> >> Thanks again
> >>
> >>> You should be able to do this with a pivot table. No code or
> >>> formulas required.
> >>>
> >>> If you are not familiar with pivot tables, see Debra Dalgleish's
> >>> site: http://www.contextures.com/tiptech.html
> >>>
> >>> Look under P .
> >>>
> >>> "ngarutoa" <whakaaro@xxxxxxxxx> wrote in message
> >>> news:9599c5290b0a8c7bcb64a9471da@xxxxxxxxxxxxxxxxxxxxx
> >>>> Any suggestions for the following would be appreciated
> >>>>
> >>>> Have a workbook with columns of data as follows:
> >>>>
> >>>> Date | Time | ID | Division | Consultant | Client ID | Problem |
> >>>> Solution
> >>>>
> >>>> Each column can have duplicates. So a client can have been
> >>>> contacted
> >>>>
> >>> multiple
> >>>
> >>>> times re the same problem by the same consultant, sometimes on the
> >>>> same
> >>>>
> >>> day
> >>>
> >>>> Would like a code that
> >>>>
> >>>> - can tell me how many and which clients have been contacted for
> >>>> the
> >>>>
> >>> Problem
> >>>
> >>>> "Crash". So if client no 7589 has had a crash should be counted as
> >>>> one for the specified date range
> >>>>
> >>>> - can tell me how many contacts all client have had in a specified
> >>>> date
> >>>>
> >>> range
> >>>
> >>>> by the Problem reported.
> >>>>
> >>>> TIA
> >>>> ngarutoa
>
>


.