excel pivot table question

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

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

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

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

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.

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

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

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

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

Hi. Thanks Teylyn. That works too. Can you tell if the one I attached called correct.xlsx was done that way?

Hi Thanks. I think I need 2010.

Great help

LOL, don't we all!

It's way better than 2007.

cheers, teylyn

Thanks for the grade!

are you using it?

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

Thanks for the tip. I had no idea.