Pivot Table Question

[CW-NEN]

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

 

[stanleydgromjr]

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
	A	B	C	D
1	City	State
2	Aurora	CO
3		IL
4		OR
5	Wharton	CO
6		IL
7		OR
8	Dover	CO
9		IL
10		OR
11	FlorhamPark	CO
12		IL
13		OR
14	JerseyCity	CO
15		IL
16		OR
17	NewYork	CO
18		IL
19		OR
Sheet1

After the macro is run:

Copy City Down If Next Cell Is Blank.xls
	A	B	C	D
1	City	State
2	Aurora	CO
3	Aurora	IL
4	Aurora	OR
5	Wharton	CO
6	Wharton	IL
7	Wharton	OR
8	Dover	CO
9	Dover	IL
10	Dover	OR
11	FlorhamPark	CO
12	FlorhamPark	IL
13	FlorhamPark	OR
14	JerseyCity	CO
15	JerseyCity	IL
16	JerseyCity	OR
17	NewYork	CO
18	NewYork	IL
19	NewYork	OR
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.

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