flatten pivotTable

hey i want to 'flatten the hierarchies' in a pivotTable and basically
repeat the duplicated rows

so right now i have

US NORTH
SOUTH
WEST
EAST
CANADA WEST
EAST

I want to change this into

US NORTH
US SOUTH
US WEST
US EAST
CANADA WEST
CANADA EAST

Isn't there any easy way to do that? I swear that it is an option
somewhere; i just can't find it.

In the programming world we call this 'flattening the hierarchial
recordset' aka when you use ADO to consume OLAP data (vs using ADOMD to
consume OLAP data)

but since I'm consuming pivotTables from 4 different sources; it isn't
really a choice for me to source the data directly
(and i must import pivotTable numbers into a database)

I think you should Access for this <g>

If it's for an import you won't need it to stay linked, so copy and paste
the entire sheet as values to break the Pivot link. Select the entire
table, do Edit / Go to / Special / Blanks, type = and then without hitting
enter hit the UP arrow once, and then hit CTRL+ENTER.

Repeat the copy paste to get rid of the formulas

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

<aaron.kempf@gmail.com> wrote in message
news:1105643733.645699.183620@c13g2000cwb.googlegroups.com...
> hey i want to 'flatten the hierarchies' in a pivotTable and basically
> repeat the duplicated rows
>
> so right now i have
>
> US NORTH
> SOUTH
> WEST
> EAST
> CANADA WEST
> EAST
>
> I want to change this into
>
> US NORTH
> US SOUTH
> US WEST
> US EAST
> CANADA WEST
> CANADA EAST
>
> Isn't there any easy way to do that? I swear that it is an option
> somewhere; i just can't find it.
>
> In the programming world we call this 'flattening the hierarchial
> recordset' aka when you use ADO to consume OLAP data (vs using ADOMD to
> consume OLAP data)
>
> but since I'm consuming pivotTables from 4 different sources; it isn't
> really a choice for me to source the data directly
> (and i must import pivotTable numbers into a database)
>

looks good thanks ken.. i'll give that a shot

at first i coudlnt find the 'tabular format (vs outline format) i was
hoping that would be what i wanted

i tink that pivotTables in Access let you do this easier.. wonder if
there is a way to turn an excel pivotTable directly into an Access
PivotTable??

Just to add to Ken's post...

It's usually easier to clean up the pivottable (remove the subtotals) than to
convert to values, then clean up.



Ken Wright wrote:
>
> I think you should Access for this <g>
>
> If it's for an import you won't need it to stay linked, so copy and paste
> the entire sheet as values to break the Pivot link. Select the entire
> table, do Edit / Go to / Special / Blanks, type = and then without hitting
> enter hit the UP arrow once, and then hit CTRL+ENTER.
>
> Repeat the copy paste to get rid of the formulas
>
> --
> Regards
> Ken....................... Microsoft MVP - Excel
> Sys Spec - Win XP Pro / XL 97/00/02/03
>
> ----------------------------------------------------------------------------
> It's easier to beg forgiveness than ask permission :-)
> ----------------------------------------------------------------------------
>
> <aaron.kempf@gmail.com> wrote in message
> news:1105643733.645699.183620@c13g2000cwb.googlegroups.com...
> > hey i want to 'flatten the hierarchies' in a pivotTable and basically
> > repeat the duplicated rows
> >
> > so right now i have
> >
> > US NORTH
> > SOUTH
> > WEST
> > EAST
> > CANADA WEST
> > EAST
> >
> > I want to change this into
> >
> > US NORTH
> > US SOUTH
> > US WEST
> > US EAST
> > CANADA WEST
> > CANADA EAST
> >
> > Isn't there any easy way to do that? I swear that it is an option
> > somewhere; i just can't find it.
> >
> > In the programming world we call this 'flattening the hierarchial
> > recordset' aka when you use ADO to consume OLAP data (vs using ADOMD to
> > consume OLAP data)
> >
> > but since I'm consuming pivotTables from 4 different sources; it isn't
> > really a choice for me to source the data directly
> > (and i must import pivotTable numbers into a database)
> >

--

Dave Peterson

Ken Wright wrote:
> If it's for an import you won't need it to stay linked, so copy and
paste
> the entire sheet as values to break the Pivot link.

Yeah, do doubt he'll be doing this manually every week <g>.
Jamie.

--

I figured this out, i just fixed it by importing into a database and
then writing a subquery to flatten

INSERT INTO dbo.F_CSHEADCOUNT
(StandardTitle, ClosedMonth, ActualPeople,
ApprovedPosition)
SELECT COALESCE ([Standard Title],
(SELECT MIN([Standard Title])
FROM staging_csHeadcount subQ
WHERE subQ.ID =
(SELECT
MAX(ID)
FROM
staging_csHeadCount subQ2
WHERE
LEN([Standard Title]) > 0 AND subQ2.ID < staging_csHeadcount.ID AND
subQ2.[Standard Title] NOT LIKE '% TOTAL')))
AS fixedStandardTItle, [Closed Month], [Actual
People], [Approved Position]
FROM dbo.staging_csHeadcount
WHERE (NOT ([Standard Title] LIKE '% Total')) OR [Standard Title]
IS NULL





onedaywhen wrote:
> Ken Wright wrote:
> > If it's for an import you won't need it to stay linked, so copy and
> paste
> > the entire sheet as values to break the Pivot link.
>
> Yeah, do doubt he'll be doing this manually every week <g>.
> Jamie.
>
> --