HELP! repeat row name in pivot table

[Jill]

Hi - I tried to figure this out for 3 hours now and am desparate, so if
someone can please help, it would be much appreciated!

In the below pivot, I am trying to get the 'state' row name to repeat for
each row.
State Agent Policy Count
PA 032404 3
032405 17
032406 5
So for example, I want it to show
State Agent Policy Count
PA 032404 3
PA 032405 17
PA 032406 5

I am using a report created by someone else and do not have access to the
data that was used to create the pivot table. I need to copy and paste the
data from their pivot table into my own report (with their permission of
course) but as there are 20,000+ rows of data, I don't want to waste the time
copying and pasting the state name down the column. I have to copy this
report data every week.

Any suggestions? Is there maybe someway to subtotal each row and then hide
the subtotal? Thanks so much!

 

[carolb]

Hi - I tried to figure this out for 3 hours now and am desparate, so if
someone can please help, it would be much appreciated!

In the below pivot, I am trying to get the 'state' row name to repeat for
each row.
State Agent Policy Count
PA 032404 3
032405 17
032406 5
So for example, I want it to show
State Agent Policy Count
PA 032404 3
PA 032405 17
PA 032406 5

I am using a report created by someone else and do not have access to the
data that was used to create the pivot table. I need to copy and paste the
data from their pivot table into my own report (with their permission of
course) but as there are 20,000+ rows of data, I don't want to waste the time
copying and pasting the state name down the column. I have to copy this
report data every week.

Any suggestions? Is there maybe someway to subtotal each row and then hide
the subtotal? Thanks so much!

 

[carolb]

Hi Jill, swap the columns on your pivot table so that Agent is first. It
will then display the state for each agent. Once you copy & paste to your
new report, you can switch the columns back and re-sort.

Hope this helps.

 

[Jill]

Hi Carol,

Thanks for the info. That would definitely work, only I forgot to mention
that the codes in the second column are not unique. In my pivot table, there
are multiple state codes, like PA, MO, CA, and there can be an agent 032404
in each of the states, so I need the final data to look like this for example:

PA 032404
MO 032404

If I switch the columns, I have the same problem. Sorry I forgot to mention
this. Any suggestions? Thanks so much!

 

[carolb]

Ouch. If there are no unique fields that won't work. Sorry that didn't
work. Can you create a macro to copy down the state names after you paste
into the new report? I've done that before. It is fairly simple using the
record function.

 

[Jill]

Macro, huh? I've never written a macro. Can you advise how I would write
one for this scenario? I deeply appreciate it.

 

[Jim Thomlinson]

Check out this link...

http://www.contextures.com/xlfaqPivot.html#Repeat

 

[jo2109]

Jill,
I have this issue all the time with data I receive.. there is a simple but
very effective solution..
=IF(ISBLANK(A5)=TRUE,D4,A5)
where "A5" is the cell in the pivot and "D4" is the cell above the formula
cell

Trust this assists.
Regards
Jo

 

[Don]

Ok, how about this, insert a few columns before and say PA appears in D3,
then in A3 put =D3 but on A4 put =if(D4="",A3,D4) then drag that down and to
the right. It should populate in the first few columns what you need?

 

[Jill]

Thank you everyone for your help. Don, I found your solution to work best.
THANKS SO MUCH! You've saved me hours of work!