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