Pivot Table Question

CW-NEN

Board Regular
Joined
Jul 18, 2007
Messages
55
I have a formatting question. I am using a pivot table. I have cities and states (these come from two different columns), on the left, and the average values generated by the data section, on the right. In some cases there are cities with the same name in more than one state. In these cases Excel does this:

Aurora CO
(Blank) IL
(Blank) OR


I am working with a lot of data and I was hoping there was a way to make it look like this:

Aurora CO
Aurora IL
Aurora OR

Without having to adjust each by hand.


Thanks
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Joined
Jul 30, 2006
Messages
3,656
CW-NEN,

Please TEST this FIRST in a COPY of your workbook.

This macro will have to be run in the raw data sheet (not in the Pivot Table sheet).

Sample data:
Copy City Down If Next Cell Is Blank.xls
ABCD
1CityState
2AuroraCO
3IL
4OR
5WhartonCO
6IL
7OR
8DoverCO
9IL
10OR
11FlorhamParkCO
12IL
13OR
14JerseyCityCO
15IL
16OR
17NewYorkCO
18IL
19OR
Sheet1



After the macro is run:
Copy City Down If Next Cell Is Blank.xls
ABCD
1CityState
2AuroraCO
3AuroraIL
4AuroraOR
5WhartonCO
6WhartonIL
7WhartonOR
8DoverCO
9DoverIL
10DoverOR
11FlorhamParkCO
12FlorhamParkIL
13FlorhamParkOR
14JerseyCityCO
15JerseyCityIL
16JerseyCityOR
17NewYorkCO
18NewYorkIL
19NewYorkOR
Sheet1



Press and hold down the 'ALT' key, and press the 'F11' key.

Insert a Module in your VBAProject, Microsoft Excel Objects

Copy the below code, and paste it into the Module1.

Code:
Option Explicit
Sub CopyCityDown()
'
' CopyCityDown Macro
' Macro created 07/30/2007 by Stanley D. Grom, Jr.
'
    Dim lngLastRow As Long
    Dim lngLoopCounter As Long
    Dim strCityHold As String

    Application.ScreenUpdating = False

    ' State column B
    ' Change the "B" in the next line of code to the column for State
    lngLastRow = Range("B" & Rows.Count).End(xlUp).Row

    ' City column A
    ' Change the "A" in the next line of code to the column for City
    strCityHold = Cells(2, "A").Value

    ' Change the "A" in the next three lines of code to the column for City
    For lngLoopCounter = 3 To lngLastRow Step 1
        If Cells(lngLoopCounter, "A").Value = "" Then
            Cells(lngLoopCounter, "A").Value = strCityHold
        Else
            strCityHold = Cells(lngLoopCounter, "A").Value
        End If
    Next lngLoopCounter

    Application.ScreenUpdating = True

End Sub


Change the column assignments in the code to fit your raw data sheet.


Please TEST this FIRST in a COPY of your workbook.

With the raw data sheet the active sheet (not in the Pivot Table sheet).

Run the 'CopyCityDown' macro in the raw data sheet.

Then in the Pivot Table, Refresh the data.

Have a great day,
Stan
 

Forum statistics

Threads
1,181,648
Messages
5,931,213
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top