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.

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

joeinnc wrote:

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

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

"Dave Peterson" wrote:

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

joeinnc wrote:

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

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.

--
Thanks,
Shane Devenshire


"joeinnc" wrote:

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.

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

ShaneDevenshire wrote:
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.



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Hi Debra,

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

--
Thanks,
Shane Devenshire


"Debra Dalgleish" wrote:

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

ShaneDevenshire wrote:
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.



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Shane,

Thanks, can't attach the worksheet but will send it to you in a seperate
e-mail.

"ShaneDevenshire" wrote:

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.

--
Thanks,
Shane Devenshire


"joeinnc" wrote:

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.

Hi Joe,

I've sent you your file with a solution if that works I will post the
solution here as well. If not let me know and we will see what can be done.

--
Thanks,
Shane Devenshire


"joeinnc" wrote:

Shane,

Thanks, can't attach the worksheet but will send it to you in a seperate
e-mail.

"ShaneDevenshire" wrote:

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.

--
Thanks,
Shane Devenshire


"joeinnc" wrote:

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.

By the way, your data is not layed out in a database (list) layout, which
means that you loose some of the functionality of the pivot table.

For the data you sent it would have been better to lay it out as follows:

Item SalesYear Amount
1 SLS_01 11111111
1 SLS_02 23445565
....

Rather than

Item SLS-01 SLS_02 ...
1 11111111 23456565
2 ...


If you want to switch data layouts (if that is doable in your situation) let
me know and I will create a formula or VBA approach to do it automatically.
--
Regards,
Shane Devenshire


"joeinnc" wrote:

Shane,

Thanks, can't attach the worksheet but will send it to you in a seperate
e-mail.

"ShaneDevenshire" wrote:

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.

--
Thanks,
Shane Devenshire


"joeinnc" wrote:

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.

Hi Debra Dalgleish,

If you choose to post this idea on your web site or add it to one of your
books please credit the solution to me.

Hi Joe,

Here are the steps for your sample data:

Assume you "Item" button is located in cell A3 as in the data you sent me.

1. To the left of the pivot table insert a column (shortcut key: Ctrl Shift +)

2. Make sure the GetPivotData feature is deactivate if you want to build the
formula via point and click. Otherwise simply type the cell addresses. You
can turn the GetPivotData feature on and off by adding the toolbar button to
the pivot table toolbar.

3. In cell A4 enter the formula =IF(NOT(ISBLANK(B4)),B4,A3) and copy it down
for the full length of the pivot table. (By the way, I can modify this
formula to work even as the pivot table grows or shrinks if that is needed.
If it only grows you just need to copy it down further.)

4. Hide column B, shortcut key: Ctrl )

5. Select cell A3 and enter "Item".

6. With A3 selected choose Data, Filter, AutoFilter

===========
Everything else is cosmetic:

If you want to hide the gridlines between each entry of a group but not
between groups as I did, you can use condition formatting:

7. Select all the formulas in column A, A4:A219 and choose the command
Format, Conditional Formatting. From the first dropdown choose Formula is
and enter =AND($A4=$A5,$A4=$A3) as the formula in the second box. Click the
Format button and choose Borders. From the Color drop down pick white. In
the Border area click the top and bottom borders. Click OK twice. This
makes the line between each group the color of Excel's built in gridlines,
which is lighter than the grids of the pivot table, if that is a problem let
me know and I will show you the modifications necessary to fix that.

If you want the text in cell A3 to look like a pivot table field button
here are the steps:

8. Select the entire pivot table and copy it. Move to a blank area of the
spreadsheet and paste it. With the new pivot table selected copy it, and
then choose Edit, Paste Special, Values. Then select the one cell with the
Item text in this range and copy it to cell A3. You can then clear the copy
of the pivot table. A3 should look like a pivot table button. If you do
this you may need to turn the AutoFilter back on and you will find a REF
error in the formula in cell A4. Just correct it to match the one in step 3
above.

------------------
For user who have their data set up in a list (database) layout there is an
entirely different approach to solve the repeated label issue.

Cheers,
Shane Devenshire

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

ShaneDevenshire wrote:
Hi Debra Dalgleish,

If you choose to post this idea on your web site or add it to one of your
books please credit the solution to me.

Hi Joe,

Here are the steps for your sample data:

Assume you "Item" button is located in cell A3 as in the data you sent me.

1. To the left of the pivot table insert a column (shortcut key: Ctrl Shift +)

2. Make sure the GetPivotData feature is deactivate if you want to build the
formula via point and click. Otherwise simply type the cell addresses. You
can turn the GetPivotData feature on and off by adding the toolbar button to
the pivot table toolbar.

3. In cell A4 enter the formula =IF(NOT(ISBLANK(B4)),B4,A3) and copy it down
for the full length of the pivot table. (By the way, I can modify this
formula to work even as the pivot table grows or shrinks if that is needed.
If it only grows you just need to copy it down further.)

4. Hide column B, shortcut key: Ctrl )

5. Select cell A3 and enter "Item".

6. With A3 selected choose Data, Filter, AutoFilter

===========
Everything else is cosmetic:

If you want to hide the gridlines between each entry of a group but not
between groups as I did, you can use condition formatting:

7. Select all the formulas in column A, A4:A219 and choose the command
Format, Conditional Formatting. From the first dropdown choose Formula is
and enter =AND($A4=$A5,$A4=$A3) as the formula in the second box. Click the
Format button and choose Borders. From the Color drop down pick white. In
the Border area click the top and bottom borders. Click OK twice. This
makes the line between each group the color of Excel's built in gridlines,
which is lighter than the grids of the pivot table, if that is a problem let
me know and I will show you the modifications necessary to fix that.

If you want the text in cell A3 to look like a pivot table field button
here are the steps:

8. Select the entire pivot table and copy it. Move to a blank area of the
spreadsheet and paste it. With the new pivot table selected copy it, and
then choose Edit, Paste Special, Values. Then select the one cell with the
Item text in this range and copy it to cell A3. You can then clear the copy
of the pivot table. A3 should look like a pivot table button. If you do
this you may need to turn the AutoFilter back on and you will find a REF
error in the formula in cell A4. Just correct it to match the one in step 3
above.

------------------
For user who have their data set up in a list (database) layout there is an
entirely different approach to solve the repeated label issue.

Cheers,
Shane Devenshire


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

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

"ShaneDevenshire" wrote:

Hi Debra Dalgleish,

If you choose to post this idea on your web site or add it to one of your
books please credit the solution to me.

Hi Joe,

Here are the steps for your sample data:

Assume you "Item" button is located in cell A3 as in the data you sent me.

1. To the left of the pivot table insert a column (shortcut key: Ctrl Shift +)

2. Make sure the GetPivotData feature is deactivate if you want to build the
formula via point and click. Otherwise simply type the cell addresses. You
can turn the GetPivotData feature on and off by adding the toolbar button to
the pivot table toolbar.

3. In cell A4 enter the formula =IF(NOT(ISBLANK(B4)),B4,A3) and copy it down
for the full length of the pivot table. (By the way, I can modify this
formula to work even as the pivot table grows or shrinks if that is needed.
If it only grows you just need to copy it down further.)

4. Hide column B, shortcut key: Ctrl )

5. Select cell A3 and enter "Item".

6. With A3 selected choose Data, Filter, AutoFilter

===========
Everything else is cosmetic:

If you want to hide the gridlines between each entry of a group but not
between groups as I did, you can use condition formatting:

7. Select all the formulas in column A, A4:A219 and choose the command
Format, Conditional Formatting. From the first dropdown choose Formula is
and enter =AND($A4=$A5,$A4=$A3) as the formula in the second box. Click the
Format button and choose Borders. From the Color drop down pick white. In
the Border area click the top and bottom borders. Click OK twice. This
makes the line between each group the color of Excel's built in gridlines,
which is lighter than the grids of the pivot table, if that is a problem let
me know and I will show you the modifications necessary to fix that.

If you want the text in cell A3 to look like a pivot table field button
here are the steps:

8. Select the entire pivot table and copy it. Move to a blank area of the
spreadsheet and paste it. With the new pivot table selected copy it, and
then choose Edit, Paste Special, Values. Then select the one cell with the
Item text in this range and copy it to cell A3. You can then clear the copy
of the pivot table. A3 should look like a pivot table button. If you do
this you may need to turn the AutoFilter back on and you will find a REF
error in the formula in cell A4. Just correct it to match the one in step 3
above.

------------------
For user who have their data set up in a list (database) layout there is an
entirely different approach to solve the repeated label issue.

Cheers,
Shane Devenshire