Pivot Table editting options

[lpexcel]

hi guys,

I am an average excel user and just needed some help with pivot tables.

Attached is a sample workbook with two sheets. The following describes this sample:
Sheet 1 - 5 columns (Store #, Street, City, State, Zip, Qty.) and like 50 rows of fake information
Sheet 2 - Pivot table where i used the columns Store #, Street, City, State, and Zip as row labels and then i put qty in values.

This almost had the right look; however, i wish i could change just a couple things.

A) As it is now, the sum of qty column lists the qty next to every row label, which looks a bit confusing. Is there a way i could have it so that just the first row label (in this case store #) is the only one that shows the value in the sum of qty column.

B) Also, and this is not quite as important because if part A is answered, i can figure this part out myself. But, is there a way to put the Street, City, State and Zip columns from sheet one together into one row label in the pivot table?

ideally id have this in my pivot table:

Row Label_____________________________Sum of qty
7 _______________________________________1
___1 abc road, generic town, NY, 55555
9________________________________________3
___2 abc road, generic town, NY, 55555
11_______________________________________2
___3 abc road, generic town, NY, 55555
GRAND TOTAL]___________________________[6

Any help would be greatly appreciated.

[Richard Buttrey]

Hi,

See attached.

I've added an extra column to your data and used this column only in the Pivot Table. The answer to your original question was just to concatenate B:E in your data and use Store and the B:E concatenated column, however that just duplicates the rows in the PT. I think the attached is probably nearer to what you want.

[JosephP]

You could just turn off subtotals and change the pivot to tabular layout then you'd have one row per store.

[lpexcel]

rich,

your attached example is exactly what i'd like, except i'd add a few comma's in the address (which i know how to do).

However, while i can replicate ur updated sheet 1, when i try and make my own pivot table based on that data (entering the same filters and row labels as you did), i still seem to have the same issue where 1001 comes before 2, 3, 301, 999, etc....

could you please let me know how you made the pivot table so that you obtained the results i would want?