pivot table- need cell indentifier to repeat in each row

[Guest]

When setting up a pivot table and your first column is a unique indentifier,
how do yuo have that repeat for each row when you pivoted data for columns to
rows.

Example

Area Week 1 Week 2 Week 3 Week 4
1 5 6 7 8

When you pivot this it shows the data like this;

Area Data Total
1 Sum of Week 1 5
Sum of Week 2 6
Sum of Week 3 7
Sum of Week 4 8


I need the area identifer to show on each row of data not just the first row.

 

[Dave Peterson]

You can't have it repeat and still keep it a pivottable.

You can copy|paste special values (in the same place or on a new worksheet) and
now it's just data--and you can do whatever you want to it--including filling in
those gaps.

Debra Dalgleish shares some techniques for filling those cells:
http://www.contextures.com/xlDataEntry02.html

 

[Guest]

Thanks Dave, we knew about that one, but because we are dealing with almost
2,000 differant identifers we wanted to see if there was an easier way,

Thank you again

 

[Guest]

Hi,

Don't give up quit so quickly. You can infact do what you want if I
understand correctly.

Send me a sample file and I will send it back showning how this can be done.
(e-mail address removed)

 

[Debra Dalgleish]

Perhaps you could also describe the solution here, so more people could
benefit from it.

 

[Guest]

Hi Debra,

Will do, when I have a sample of the data to make sure we are all on the
same page.

 

[Debra Dalgleish]

Thanks. If I ever post your solution, I'll be sure to give you credit.

 

[Liz]

Hello Shane,

I realize this post is very old, but I was wondering if you would mind
helping me? or if you knew of any updates to this post?

I was able to use your formula below to create multiple labels for rows,
however when there's more than one label in the column, I'm finding the
formula doesn't update for a new row label.

Example:
Axis, Alabama Shanghai, China
Antwerp, Belgium
Santos, Brazil
El Paso, Illinois Rio de Janeiro, Brazil
Livorno, Italy
Port Kelang, Malaysia

I created the formula and pulled it down for the entire column and it
created Axis, Alabama for each row, even the ones that fell under El Paso -
Livorno and Port Kelang. (Except for the row actually labeled El Paso, it
did pull that one....)

I can send you a spreadsheet showing what I'm talking about.

If you could help that would be great, but if not, I'll keep checking around
on my own. Your response was the only post that made sense on microsoft's
website. I like your formula, but if there's multiple row labels I think it
might be easier to just copy and paste down the columns....

In cell A4 enter the formula =IF(NOT(ISBLANK(B4)),B4,A3) and copy it down
for the full length of the pivot table.

Thank you,
Liz

 

[Liz]

Hello Shane,

I realize this post is very old, but I was wondering if you would mind
helping me? or if you knew of any updates to this post?

I was able to use your formula below to create multiple labels for rows,
however when there's more than one label in the column, I'm finding the
formula doesn't update for a new row label.

Example:
Axis, Alabama Shanghai, China
Antwerp, Belgium
Santos, Brazil
El Paso, Illinois Rio de Janeiro, Brazil
Livorno, Italy
Port Kelang, Malaysia

I created the formula and pulled it down for the entire column and it
created Axis, Alabama for each row, even the ones that fell under El Paso -
Livorno and Port Kelang. (Except for the row actually labeled El Paso, it
did pull that one....)

I can send you a spreadsheet showing what I'm talking about.

If you could help that would be great, but if not, I'll keep checking around
on my own. Your response was the only post that made sense on microsoft's
website. I like your formula, but if there's multiple row labels I think it
might be easier to just copy and paste down the columns....

In cell A4 enter the formula =IF(NOT(ISBLANK(B4)),B4,A3) and copy it down
for the full length of the pivot table.

Thank you,
Liz