We help IT Professionals succeed at work.

excel pivot table question

onyourmark
onyourmark asked
on
Hello. I am having a problem when making a piviot table. I am attaching the file. When I select two fields for the Row Labels, they both end up in the same column. I want them to be in two separate columns. Can someone say why this is happening?

Thanks.
cancunGeoSample.xlsx
Comment
Watch Question

Top Expert 2010

Commented:
Hello onyourmark,

That is the default behavior for Excel 2007 PivotTables.

To get the "classic layout as in Excel 2003...

1) Select a cell in the PT

2) On the ribbon, go to PivotTable Tools/Options

3) In the PivotTable group, click Options

4) In the dialog box, go to the Data tab, and check Classic PivotTable layout

Regards,

Patrick
Most Valuable Expert 2011
Awarded 2010

Commented:
Hello onyourmark,

Click the Design tab on the Pivot Table Tools, then, click the Report Layout drop-down and select a different report layout, for example "Show in tabular form" and "Repeat all Item Labels". Then select the Subtotals dropdown and click "Do not show subtotals"

see attached.

cheers, teylyn
Copy-of-cancunGeoSample.xlsx

Author

Commented:
Hello. Thank you. I tried but it is not working right. I am attaching what I have. Why are some of the entries blank (like the first entry in row 6). By the way, I want to have all the longitude and latitude pairs be unique. I want to group them so that repeats of such pairs are pushed together into one row and the sentOrEmo is averaged over these.
I could not find the "repeat all Items label" either.
cancunGeoSample.xlsx
Most Valuable Expert 2011
Awarded 2010

Commented:
Hello onyourmark,

I'm afraid I sent you on a wild goose chase. The "repeate all item labels" command is a new feature in Excel 2010, which I was using to construct the pivot table. I checked in 2007 now, and there is no way to make the row labels repeat. Sorry. Microsoft Excel MVP and Pivot Table authority Debra Dalgliesh says this on her site:

"The row headings show once in a PivotTable, and there's no setting you can change, to force them to repeat. To create a table with a heading on each row, you could copy the pivot table, paste it as values in another location, and fill in the blanks. In the beta version of Excel 2010, this feature is finally available." (http://www.contextures.com/xlfaqpivot.html#Repeat)

In the pivot table, all Long/Lat pairs _are_ unique. With the settings you have applied, the values in sentOrEmo will be averaged for several Long/Lat occurrences in the source data.

cheers, teylyn

Author

Commented:
Hello. I think I did not make myself clear. I am sure it must be possible to do what I want since it is a very simple thing. The spreadsheet has a number of columns C1, C2, ... CN. All I want is a pivot table with 3 columns. C1, C2 and C3* where C3* is an average.
C1, C2 may have repeats. Like for example if C1 is first name and C2 is last name, perhaps the person is repeated several times in the spreadsheet (i.e. occurs in several rows). In this case, in the pivot table, I want the average of C3 over all the repeats of C1-C2.
For example maybe C3 is the number of hours the person sleeps. Then there should only be one row in the pivot table for this person and in its C3* column will be the average number of hours slept for this person.
Most Valuable Expert 2011
Awarded 2010

Commented:
Yes, it is a very simple thing. That's what the pivot table does. And yours is already doing it.

Of all the repeats for the same Lat/Long combination, you see only one mention in the pivot table. The values are the average values for these repeats for the data in column sentOrEmo.

Average of sentOrEmo            
Latitude      Longitude      Total
-25.417      -49.25      1
-22.9      -47.083      7
      -43.233      0.5

I don't know if the above will be spaced correctly, and I have no way of correcting the spacing after I hit send. The third row of data in this pivot table is for the coordinates -22.9 lat / -43.233 long.  There were several value for this combination in your source data. The pivot table shows the average of all the values in the source data in column sentOrEmo.

The blank cells in the first column of the pivot tables appear when the same Latitude value has several related Longitude values. A bit like having several people with the same last name, but different first names in a table that goes Last name -  First name - Hours slept.

So, the pivot table is working as it should.

cheers, teylyn


Author

Commented:
Thanks. But if I copy and paste that pivot table into another sheet (see the attached file and the sheet called CopyAndPaste cell A3 is empty. I know that that means that it has the same value as A2 but I need A3 filled in. Is there a way to do that?
cancunGeoSample2.xlsx
Most Valuable Expert 2011
Awarded 2010

Commented:
Yes, there is.

select column A
Hit the F5 key and click Special
Tick Blanks and hit OK
now all blank cells are selected and the first blank cell is the active cell.
type a = sign and hit the up arrow
hold down the Ctrl key and hit Enter
Now the previously empty cells have a formula that references the cell directly above.

You can copy column A and use Paste Special - Values to replace the formulas with the values.

cheers, teylyn

Author

Commented:
Here is a correct one but I don't know how it was obtained
correct.xlsx

Author

Commented:
Hi. Thanks Teylyn. That works too. Can you tell if the one I attached called correct.xlsx was done that way?
Most Valuable Expert 2011
Awarded 2010
Commented:
onyourmark,

the latest attached file has only unique values in column A. There are no blank cells. So, it's probable that the copy taken from that pivot table was a straightforward copy and paste without any subsequent manipulation of the pasted data.

Even if there were blanks in column A and a method to fill in the blanks (like the one I describe above) had been used, I don't think it would be possible to tell whether or not this was the case.

Since with Excel 2010 there now exists the option to repeat the item labels, a table with repeating labels could have been created that way.

Does that answer your question?

cheers, teylyn

Author

Commented:
Hi Thanks. I think I need 2010.

Author

Commented:
Great help
Most Valuable Expert 2011
Awarded 2010

Commented:
LOL, don't we all!

It's way better than 2007.

cheers, teylyn
Most Valuable Expert 2011
Awarded 2010

Commented:
Thanks for the grade!

Author

Commented:
are you using it?
Most Valuable Expert 2011
Awarded 2010

Commented:
I'm running 2010 beta and 2007 on my private lapper, and on my company machine I have 2003 and 2010 beta.

I absolutely, positively hate 2007, but 2010 is way better. That's why I hardly open 2007 these days, unless I must.

I hope that my company will upgrade to 2010 before the beta expires.

cheers, teylyn

Author

Commented:
Thanks for the tip. I had no idea.