I have a list of places our reps visit along with who they visited and
various other things. I then bring this data into a pivot table.

I want to see a total of how many times they visited each Town, but I only
want the town counted once of any date, so if they visit London on the
02/07/07 and went to 4 placed there will be 4 lines in the data but the pivot
table only counts 1. But if they went to london on the 02/07/07 and then
again on the 05/07/07 then the pivot table will count 2.

Anybody got any ideas how this can be none?

Not sure how to do that within a pivot table but you could probably do it
outside of the pivot on the raw data.

If you want to go that route give me the details of the layout (and be as
specific as you can).

Biff
Microsoft Excel MVP

"Keith" <(E-Mail Removed)> wrote in message
news:23343F90-7A86-4F2E-9C89-(E-Mail Removed)...
>I have a list of places our reps visit along with who they visited and
> various other things. I then bring this data into a pivot table.
>
> I want to see a total of how many times they visited each Town, but I only
> want the town counted once of any date, so if they visit London on the
> 02/07/07 and went to 4 placed there will be 4 lines in the data but the
> pivot
> table only counts 1. But if they went to london on the 02/07/07 and then
> again on the 05/07/07 then the pivot table will count 2.
>
> Anybody got any ideas how this can be none?

Hi Keith

You could add another column to your source table called Count.
With Date of visit in column A, and Town in column B, then
in cell 2 of the new column enter
=IF(SUMPRODUCT(($A$2:$A1000=A2)*($B$2:$B1000=B2))=1,1,"")
and copy down.
Add Count to the Data area of your PT, as Sum of Count.
--
Regards

Roger Govier


"Keith" <(E-Mail Removed)> wrote in message
news:23343F90-7A86-4F2E-9C89-(E-Mail Removed)...
>I have a list of places our reps visit along with who they visited and
> various other things. I then bring this data into a pivot table.
>
> I want to see a total of how many times they visited each Town, but I
> only
> want the town counted once of any date, so if they visit London on the
> 02/07/07 and went to 4 placed there will be 4 lines in the data but
> the pivot
> table only counts 1. But if they went to london on the 02/07/07 and
> then
> again on the 05/07/07 then the pivot table will count 2.
>
> Anybody got any ideas how this can be none?

OK,

An abbreviated version would be

Column A Date
Column B Town
Column C Hospital
Column D Person or Department Visited
Column E Rep

So we could have a few entries like this:

02/07/07 Edinburgh Edinburgh Royal Ward 2 John
02/07/07 Edinburgh Western General A and E John
02/07/07 Glasgow Western General Maternity John
03/07/07 Glasgow Monklands Physiomed John

In the above case the pivot table says that Edinburgh was visited twice as
was Glasgow, but Edinburgh was visited only once since it was all done on the
same day, but Glasgow was visited twice since the rep went on 2 separate days.

The list also contains more than one rep so we can limit the pivot table to
look at just one rep if we wish or all of them.

Does this help?



"T. Valko" wrote:

> Not sure how to do that within a pivot table but you could probably do it
> outside of the pivot on the raw data.
>
> If you want to go that route give me the details of the layout (and be as
> specific as you can).
>
> Biff
> Microsoft Excel MVP
>
> "Keith" <(E-Mail Removed)> wrote in message
> news:23343F90-7A86-4F2E-9C89-(E-Mail Removed)...
> >I have a list of places our reps visit along with who they visited and
> > various other things. I then bring this data into a pivot table.
> >
> > I want to see a total of how many times they visited each Town, but I only
> > want the town counted once of any date, so if they visit London on the
> > 02/07/07 and went to 4 placed there will be 4 lines in the data but the
> > pivot
> > table only counts 1. But if they went to london on the 02/07/07 and then
> > again on the 05/07/07 then the pivot table will count 2.
> >
> > Anybody got any ideas how this can be none?
>
>
>

Hi Keith

I responded to your original post.
In case you missed it, here is what I wrote

You could add another column to your source table called Count.
With Date of visit in column A, and Town in column B, then
in cell 2 of the new column enter
=IF(SUMPRODUCT(($A$2:$A1000=A2)*($B$2:$B1000=B2))=1,1,"")
and copy down.
Add Count to the Data area of your PT, as Sum of Count

--
Regards

Roger Govier


"Keith" <(E-Mail Removed)> wrote in message
news:68C82FA4-12AF-4FD9-8B3B-(E-Mail Removed)...
> OK,
>
> An abbreviated version would be
>
> Column A Date
> Column B Town
> Column C Hospital
> Column D Person or Department Visited
> Column E Rep
>
> So we could have a few entries like this:
>
> 02/07/07 Edinburgh Edinburgh Royal Ward 2 John
> 02/07/07 Edinburgh Western General A and E John
> 02/07/07 Glasgow Western General Maternity John
> 03/07/07 Glasgow Monklands Physiomed John
>
> In the above case the pivot table says that Edinburgh was visited
> twice as
> was Glasgow, but Edinburgh was visited only once since it was all done
> on the
> same day, but Glasgow was visited twice since the rep went on 2
> separate days.
>
> The list also contains more than one rep so we can limit the pivot
> table to
> look at just one rep if we wish or all of them.
>
> Does this help?
>
>
>
> "T. Valko" wrote:
>
>> Not sure how to do that within a pivot table but you could probably
>> do it
>> outside of the pivot on the raw data.
>>
>> If you want to go that route give me the details of the layout (and
>> be as
>> specific as you can).
>>
>> Biff
>> Microsoft Excel MVP
>>
>> "Keith" <(E-Mail Removed)> wrote in message
>> news:23343F90-7A86-4F2E-9C89-(E-Mail Removed)...
>> >I have a list of places our reps visit along with who they visited
>> >and
>> > various other things. I then bring this data into a pivot table.
>> >
>> > I want to see a total of how many times they visited each Town, but
>> > I only
>> > want the town counted once of any date, so if they visit London on
>> > the
>> > 02/07/07 and went to 4 placed there will be 4 lines in the data but
>> > the
>> > pivot
>> > table only counts 1. But if they went to london on the 02/07/07
>> > and then
>> > again on the 05/07/07 then the pivot table will count 2.
>> >
>> > Anybody got any ideas how this can be none?
>>
>>
>>

Hi Roger,

I tried your sugestion but it didn't work

21/05/07 Stoke on Trent 1
21/05/07 London
21/05/07 London
22/05/07 Birmingham 1

As you can see from the above example there are 4 visits here. Three are on
the 21/05/07 and one on the 22/05/07.

The first and fourth lines are fine, but The two london entries sould have
counted as 1 as well, since he went to London in the 21/05/07 as well as
Stoke.


"Roger Govier" wrote:

> Hi Keith
>
> You could add another column to your source table called Count.
> With Date of visit in column A, and Town in column B, then
> in cell 2 of the new column enter
> =IF(SUMPRODUCT(($A$2:$A1000=A2)*($B$2:$B1000=B2))=1,1,"")
> and copy down.
> Add Count to the Data area of your PT, as Sum of Count.
> --
> Regards
>
> Roger Govier
>
>
> "Keith" <(E-Mail Removed)> wrote in message
> news:23343F90-7A86-4F2E-9C89-(E-Mail Removed)...
> >I have a list of places our reps visit along with who they visited and
> > various other things. I then bring this data into a pivot table.
> >
> > I want to see a total of how many times they visited each Town, but I
> > only
> > want the town counted once of any date, so if they visit London on the
> > 02/07/07 and went to 4 placed there will be 4 lines in the data but
> > the pivot
> > table only counts 1. But if they went to london on the 02/07/07 and
> > then
> > again on the 05/07/07 then the pivot table will count 2.
> >
> > Anybody got any ideas how this can be none?
>
>
>

Hi Keith

Sorry, the formula should have been
=IF(SUMPRODUCT(($A$2:A2=A2)*($B$2:B2=B2))=1,1,"")
and copied down

The range grows as you copy down, as the second part of each range is
relative, and the first is absolute.

--
Regards

Roger Govier


"Keith" <(E-Mail Removed)> wrote in message
news:2C3E486C-48D4-495A-9F66-(E-Mail Removed)...
> Hi Roger,
>
> I tried your sugestion but it didn't work
>
> 21/05/07 Stoke on Trent 1
> 21/05/07 London
> 21/05/07 London
> 22/05/07 Birmingham 1
>
> As you can see from the above example there are 4 visits here. Three
> are on
> the 21/05/07 and one on the 22/05/07.
>
> The first and fourth lines are fine, but The two london entries sould
> have
> counted as 1 as well, since he went to London in the 21/05/07 as well
> as
> Stoke.
>
>
> "Roger Govier" wrote:
>
>> Hi Keith
>>
>> You could add another column to your source table called Count.
>> With Date of visit in column A, and Town in column B, then
>> in cell 2 of the new column enter
>> =IF(SUMPRODUCT(($A$2:$A1000=A2)*($B$2:$B1000=B2))=1,1,"")
>> and copy down.
>> Add Count to the Data area of your PT, as Sum of Count.
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "Keith" <(E-Mail Removed)> wrote in message
>> news:23343F90-7A86-4F2E-9C89-(E-Mail Removed)...
>> >I have a list of places our reps visit along with who they visited
>> >and
>> > various other things. I then bring this data into a pivot table.
>> >
>> > I want to see a total of how many times they visited each Town, but
>> > I
>> > only
>> > want the town counted once of any date, so if they visit London on
>> > the
>> > 02/07/07 and went to 4 placed there will be 4 lines in the data but
>> > the pivot
>> > table only counts 1. But if they went to london on the 02/07/07
>> > and
>> > then
>> > again on the 05/07/07 then the pivot table will count 2.
>> >
>> > Anybody got any ideas how this can be none?
>>
>>
>>