Pivot Table - problem with repeated fields

[ZikO]

Hi

My data in table are very simply organised as below:

---------------------------------------------
Res.no City Code Var1 Var2 Var3
---------------------------------------------
1 City1 CD0001 num num num
2 City1 CD0002 num num num
3 City1 CD0003 num num num
4 City1 CD0004 num num num
5 City1 CD0005 num num num
6 City2 CD0006 num num num
7 City2 CD0007 num num num
8 City2 CD0008 num num num
9 City2 CD0009 num num num
10 City3 CD0010 num num num
11 City4 CD0011 num num num
12 City3 CD0012 num num num
13 City3 CD0013 num num num
14 City3 CD0014 num num num
15 City1 CD0015 num num num
---------------------------------------------

etc.

I have created pivot table to count a number of Cities, the average of
num from particular columns, min, max and standard dev.



1. First problem: Excel, for some reasons repeates, a few cities in the
fields in Pivot Table such that Pivot Table looks similat similar as below

-----------------------------------------------
Count average min max std.dev
-----------------------------------------------
City1 5 num num num num
City1 1 num num num num
City2 4 num num num num
City3 3 num num num num
City3 1 num num num num
City4 1 num num num num
-----------------------------------------------

Instead of counting properly for instance City1 and City3 etc.

-----------------------------------------------
Count average min max std.dev
-----------------------------------------------
City1 6 num num num num
City2 4 num num num num
City3 4 num num num num
City4 1 num num num num
-----------------------------------------------

Any idea what could be wrong? I tried to remove all blank spaces and
blank characters in city names but it does not help.




2. The second problem I found is that I don't know how to apply Pivot
Table for Filtered Source Data (using Filter from Data Tab). Pivot Table
does not seem to include the data would be reduced due to for example
excluding zeros etc.




I will be very grateful for any suggestions.

Regards

Zbigniew

 

[Harry Flashman]

Hi

My data in table are very simply organised as below:

---------------------------------------------
Res.no  City    Code    Var1    Var2    Var3
---------------------------------------------
1       City1   CD0001  num     num     num
2       City1   CD0002  num     num     num
3       City1   CD0003  num     num     num
4       City1   CD0004  num     num     num
5       City1   CD0005  num     num     num
6       City2   CD0006  num     num     num
7       City2   CD0007  num     num     num
8       City2   CD0008  num     num     num
9       City2   CD0009  num     num     num
10      City3   CD0010  num     num     num
11      City4   CD0011  num     num     num
12      City3   CD0012  num     num     num
13      City3   CD0013  num     num     num
14      City3   CD0014  num     num     num
15      City1   CD0015  num     num     num
---------------------------------------------

etc.

I have created pivot table to count a number of Cities, the average of
num from particular columns, min, max and standard dev.

1. First problem: Excel, for some reasons repeates, a few cities in the
fields in Pivot Table such that Pivot Table looks similat similar as below

-----------------------------------------------
        Count   average min     max     std.dev
-----------------------------------------------
City1   5       num     num     num     num
City1   1       num     num     num     num
City2   4       num     num     num     num
City3   3       num     num     num     num
City3   1       num     num     num     num
City4   1       num     num     num     num
-----------------------------------------------

Instead of counting properly for instance City1 and City3 etc.

-----------------------------------------------
        Count   average min     max     std.dev
-----------------------------------------------
City1   6       num     num     num     num
City2   4       num     num     num     num
City3   4       num     num     num     num
City4   1       num     num     num     num
-----------------------------------------------

Any idea what could be wrong? I tried to remove all blank spaces and
blank characters in city names but it does not help.

2. The second problem I found is that I don't know how to apply Pivot
Table for Filtered Source Data (using Filter from Data Tab). Pivot Table
does not seem to include the data would be reduced due to for example
excluding zeros etc.

I will be very grateful for any suggestions.

Regards

Zbigniew

If you copy column with the city names to a new worksheet and then
remove duplicates (Data/Data Tools in Excel 2007) does it leave a
unique list of cities or does there appear to be duplicates?
With your second question, I don't know answer but I will be
interested to hear the solution.

 

[Harry Flashman]

Hi

My data in table are very simply organised as below:

---------------------------------------------
Res.no  City    Code    Var1    Var2    Var3
---------------------------------------------
1       City1   CD0001  num     num     num
2       City1   CD0002  num     num     num
3       City1   CD0003  num     num     num
4       City1   CD0004  num     num     num
5       City1   CD0005  num     num     num
6       City2   CD0006  num     num     num
7       City2   CD0007  num     num     num
8       City2   CD0008  num     num     num
9       City2   CD0009  num     num     num
10      City3   CD0010  num     num     num
11      City4   CD0011  num     num     num
12      City3   CD0012  num     num     num
13      City3   CD0013  num     num     num
14      City3   CD0014  num     num     num
15      City1   CD0015  num     num     num
---------------------------------------------

etc.

I have created pivot table to count a number of Cities, the average of
num from particular columns, min, max and standard dev.

1. First problem: Excel, for some reasons repeates, a few cities in the
fields in Pivot Table such that Pivot Table looks similat similar as below

-----------------------------------------------
        Count   average min     max     std.dev
-----------------------------------------------
City1   5       num     num     num     num
City1   1       num     num     num     num
City2   4       num     num     num     num
City3   3       num     num     num     num
City3   1       num     num     num     num
City4   1       num     num     num     num
-----------------------------------------------

Instead of counting properly for instance City1 and City3 etc.

-----------------------------------------------
        Count   average min     max     std.dev
-----------------------------------------------
City1   6       num     num     num     num
City2   4       num     num     num     num
City3   4       num     num     num     num
City4   1       num     num     num     num
-----------------------------------------------

Any idea what could be wrong? I tried to remove all blank spaces and
blank characters in city names but it does not help.

2. The second problem I found is that I don't know how to apply Pivot
Table for Filtered Source Data (using Filter from Data Tab). Pivot Table
does not seem to include the data would be reduced due to for example
excluding zeros etc.

I will be very grateful for any suggestions.

Regards

Zbigniew

Sorry I just read your post properly. I see now that there are
duplicates.
I am not sure what the solution is for that then. I have situations
where what appear to blank cells are not blank (apparently) but I
can't see any characters in the cells. I don't know what is going on
there. I'll be reading this thread. I hope you get answers from people
who are more knowledgeable than me.
Maybe once you remove true duplicates and then past the data into some
text editing program you might see some characters that aren't visible
in Excel?

 

[Dave Peterson]

You have differences in the spelling of the "same" city names.

"City1" and "City1 " (with an extra trailing space) or "City 1" (with an extra
embedded space.

You may have to look very closely (inspecting each character in each cell using
the formula bar????) to find the differences, but they're there.

 

[ZikO]

You have differences in the spelling of the "same" city names.

"City1" and "City1 " (with an extra trailing space) or "City 1" (with an
extra embedded space.

You may have to look very closely (inspecting each character in each
cell using the formula bar????) to find the differences, but they're there.

thank for answer.

It seems like I have something extra in those cells and Filter from Data
tab (Ribbon) seems to avoid those little mismatches.

Thanks

Regards

 

[ZikO]

Sorry I just read your post properly. I see now that there are

duplicates.
I am not sure what the solution is for that then. I have situations
where what appear to blank cells are not blank (apparently) but I
can't see any characters in the cells. I don't know what is going on
there. I'll be reading this thread. I hope you get answers from people
who are more knowledgeable than me.
Maybe once you remove true duplicates and then past the data into some
text editing program you might see some characters that aren't visible
in Excel?

Np.

I have found that if you have extra white spaces before name such as:
[Space]Aaaa'
Aaaa
Aaaa[space]

etc.

Then Pivot table will show first name as Blank, second and third name
separately.

I hope it can help you.

Regards