Counting for unique values using 2 cells
 

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?

Counting for unique values using 2 cells
 

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" wrote in message
...
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?

Counting for unique values using 2 cells
 

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" wrote in message
...
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?

Counting for unique values using 2 cells
 

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" wrote in message
...
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?

Counting for unique values using 2 cells
 

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" wrote in message
...
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" wrote in message
...
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?

Counting for unique values using 2 cells
 

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" wrote in message
...
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?

Counting for unique values using 2 cells
 

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" wrote in message
...
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" wrote in message
...
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?