Calculate cell row and column

I have a case where I have a pivot table one ROW designator with about 20
values. I also have about 10 values. What I want to do is have the data
listed as

ROW 1 Data value 1 Data value 2 Data value 3 instead of

Row 1 Data value1
Data value2
Data value3
Row 2 Data value1

etc.

Is there a way to do this that isn't tedious?

Thanks in advance,
Barb Reinhardt

Barb,

Drag the Data Value button to the top of the columns.

HTH,
Bernie
MS Excel MVP


"Barb R." <[email protected]> wrote in message
news:[email protected]...
> I have a case where I have a pivot table one ROW designator with about 20
> values. I also have about 10 values. What I want to do is have the
data
> listed as
>
> ROW 1 Data value 1 Data value 2 Data value 3 instead of
>
> Row 1 Data value1
> Data value2
> Data value3
> Row 2 Data value1
>
> etc.
>
> Is there a way to do this that isn't tedious?
>
> Thanks in advance,
> Barb Reinhardt

Tried that. It didn't work. I have data for 10-12 subprograms summarized
as data for several metrics. I want to summarize each metric by subprogram.
If I put the metric at the top of the column, it summarizes it by value for
the metric.

Did I miss something?

I opted to use the "Transpose" function, but it's not as elegant as I'd like.

Barb Reinhardt

"Bernie Deitrick" wrote:

> Barb,
>
> Drag the Data Value button to the top of the columns.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Barb R." <[email protected]> wrote in message
> news:[email protected]...
> > I have a case where I have a pivot table one ROW designator with about 20
> > values. I also have about 10 values. What I want to do is have the
> data
> > listed as
> >
> > ROW 1 Data value 1 Data value 2 Data value 3 instead of
> >
> > Row 1 Data value1
> > Data value2
> > Data value3
> > Row 2 Data value1
> >
> > etc.
> >
> > Is there a way to do this that isn't tedious?
> >
> > Thanks in advance,
> > Barb Reinhardt
>
>
>

Barb,

If you could post a small table of your data, the result you get, and the
result you want, then it would help clarify what you mean.

HTH,
Bernie
MS Excel MVP


"Barb R." <[email protected]> wrote in message
news:[email protected]...
> Tried that. It didn't work. I have data for 10-12 subprograms
summarized
> as data for several metrics. I want to summarize each metric by
subprogram.
> If I put the metric at the top of the column, it summarizes it by value
for
> the metric.
>
> Did I miss something?
>
> I opted to use the "Transpose" function, but it's not as elegant as I'd
like.
>
> Barb Reinhardt
>
> "Bernie Deitrick" wrote:
>
> > Barb,
> >
> > Drag the Data Value button to the top of the columns.
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "Barb R." <[email protected]> wrote in message
> > news:[email protected]...
> > > I have a case where I have a pivot table one ROW designator with about
20
> > > values. I also have about 10 values. What I want to do is have the
> > data
> > > listed as
> > >
> > > ROW 1 Data value 1 Data value 2 Data value 3 instead of
> > >
> > > Row 1 Data value1
> > > Data value2
> > > Data value3
> > > Row 2 Data value1
> > >
> > > etc.
> > >
> > > Is there a way to do this that isn't tedious?
> > >
> > > Thanks in advance,
> > > Barb Reinhardt
> >
> >
> >

OK, here is an attempt at an example without real data
Metric 1 Metric 2 Metric 3 Metric 4
Group 1
Group 2
Group 3
Group 1
Group 2
Group 3
Group 1
Group 2
Group 3
Group 1
Group 2
Group 3

I want to sum metric 1, 2, 3, etc. or each Group. My pivot table looks
like this:

Group 1 Metric1
Metric2
Metric3
Group 2 Metric1
Metric2
Metric3
Group 3 Metric1
Metric2
Metric3

And I want
Metric 1 Metric 2 Metric 3
Sum Sum Sum
Group 1
Group 2
Group 3

"Bernie Deitrick" wrote:

> Barb,
>
> If you could post a small table of your data, the result you get, and the
> result you want, then it would help clarify what you mean.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Barb R." <[email protected]> wrote in message
> news:[email protected]...
> > Tried that. It didn't work. I have data for 10-12 subprograms
> summarized
> > as data for several metrics. I want to summarize each metric by
> subprogram.
> > If I put the metric at the top of the column, it summarizes it by value
> for
> > the metric.
> >
> > Did I miss something?
> >
> > I opted to use the "Transpose" function, but it's not as elegant as I'd
> like.
> >
> > Barb Reinhardt
> >
> > "Bernie Deitrick" wrote:
> >
> > > Barb,
> > >
> > > Drag the Data Value button to the top of the columns.
> > >
> > > HTH,
> > > Bernie
> > > MS Excel MVP
> > >
> > >
> > > "Barb R." <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I have a case where I have a pivot table one ROW designator with about
> 20
> > > > values. I also have about 10 values. What I want to do is have the
> > > data
> > > > listed as
> > > >
> > > > ROW 1 Data value 1 Data value 2 Data value 3 instead of
> > > >
> > > > Row 1 Data value1
> > > > Data value2
> > > > Data value3
> > > > Row 2 Data value1
> > > >
> > > > etc.
> > > >
> > > > Is there a way to do this that isn't tedious?
> > > >
> > > > Thanks in advance,
> > > > Barb Reinhardt
> > >
> > >
> > >
>
>
>

Barb,

You don't actually have a database: you have a cross-tab table. To get it
to work the way you want, you need to "un-cross-tab" your data, by using a
pivot table in a special way, described at:

http://j-walk.com/ss/excel/usertips/tip068.htm

Once you've done that, you can use a pivot table in a normal way to get the
result you want.

HTH,
Bernie
MS Excel MVP


"Barb R." <[email protected]> wrote in message
news:[email protected]...
> OK, here is an attempt at an example without real data
> Metric 1 Metric 2 Metric 3 Metric 4
> Group 1
> Group 2
> Group 3
> Group 1
> Group 2
> Group 3
> Group 1
> Group 2
> Group 3
> Group 1
> Group 2
> Group 3
>
> I want to sum metric 1, 2, 3, etc. or each Group. My pivot table looks
> like this:
>
> Group 1 Metric1
> Metric2
> Metric3
> Group 2 Metric1
> Metric2
> Metric3
> Group 3 Metric1
> Metric2
> Metric3
>
> And I want
> Metric 1 Metric 2 Metric 3
> Sum Sum Sum
> Group 1
> Group 2
> Group 3
>
> "Bernie Deitrick" wrote:
>
> > Barb,
> >
> > If you could post a small table of your data, the result you get, and
the
> > result you want, then it would help clarify what you mean.
> >
> > HTH,
> > Bernie
> > MS Excel MVP
> >
> >
> > "Barb R." <[email protected]> wrote in message
> > news:[email protected]...
> > > Tried that. It didn't work. I have data for 10-12 subprograms
> > summarized
> > > as data for several metrics. I want to summarize each metric by
> > subprogram.
> > > If I put the metric at the top of the column, it summarizes it by
value
> > for
> > > the metric.
> > >
> > > Did I miss something?
> > >
> > > I opted to use the "Transpose" function, but it's not as elegant as
I'd
> > like.
> > >
> > > Barb Reinhardt
> > >
> > > "Bernie Deitrick" wrote:
> > >
> > > > Barb,
> > > >
> > > > Drag the Data Value button to the top of the columns.
> > > >
> > > > HTH,
> > > > Bernie
> > > > MS Excel MVP
> > > >
> > > >
> > > > "Barb R." <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > I have a case where I have a pivot table one ROW designator with
about
> > 20
> > > > > values. I also have about 10 values. What I want to do is have
the
> > > > data
> > > > > listed as
> > > > >
> > > > > ROW 1 Data value 1 Data value 2 Data value 3 instead of
> > > > >
> > > > > Row 1 Data value1
> > > > > Data value2
> > > > > Data value3
> > > > > Row 2 Data value1
> > > > >
> > > > > etc.
> > > > >
> > > > > Is there a way to do this that isn't tedious?
> > > > >
> > > > > Thanks in advance,
> > > > > Barb Reinhardt
> > > >
> > > >
> > > >
> >
> >
> >

I never said I had a database. I'll take a look and see what I can glean
from the link.

Thanks,
Barb Reinhardt

"Bernie Deitrick" wrote:

> Barb,
>
> You don't actually have a database: you have a cross-tab table. To get it
> to work the way you want, you need to "un-cross-tab" your data, by using a
> pivot table in a special way, described at:
>
> http://j-walk.com/ss/excel/usertips/tip068.htm
>
> Once you've done that, you can use a pivot table in a normal way to get the
> result you want.
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "Barb R." <[email protected]> wrote in message
> news:[email protected]...
> > OK, here is an attempt at an example without real data
> > Metric 1 Metric 2 Metric 3 Metric 4
> > Group 1
> > Group 2
> > Group 3
> > Group 1
> > Group 2
> > Group 3
> > Group 1
> > Group 2
> > Group 3
> > Group 1
> > Group 2
> > Group 3
> >
> > I want to sum metric 1, 2, 3, etc. or each Group. My pivot table looks
> > like this:
> >
> > Group 1 Metric1
> > Metric2
> > Metric3
> > Group 2 Metric1
> > Metric2
> > Metric3
> > Group 3 Metric1
> > Metric2
> > Metric3
> >
> > And I want
> > Metric 1 Metric 2 Metric 3
> > Sum Sum Sum
> > Group 1
> > Group 2
> > Group 3
> >
> > "Bernie Deitrick" wrote:
> >
> > > Barb,
> > >
> > > If you could post a small table of your data, the result you get, and
> the
> > > result you want, then it would help clarify what you mean.
> > >
> > > HTH,
> > > Bernie
> > > MS Excel MVP
> > >
> > >
> > > "Barb R." <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Tried that. It didn't work. I have data for 10-12 subprograms
> > > summarized
> > > > as data for several metrics. I want to summarize each metric by
> > > subprogram.
> > > > If I put the metric at the top of the column, it summarizes it by
> value
> > > for
> > > > the metric.
> > > >
> > > > Did I miss something?
> > > >
> > > > I opted to use the "Transpose" function, but it's not as elegant as
> I'd
> > > like.
> > > >
> > > > Barb Reinhardt
> > > >
> > > > "Bernie Deitrick" wrote:
> > > >
> > > > > Barb,
> > > > >
> > > > > Drag the Data Value button to the top of the columns.
> > > > >
> > > > > HTH,
> > > > > Bernie
> > > > > MS Excel MVP
> > > > >
> > > > >
> > > > > "Barb R." <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > I have a case where I have a pivot table one ROW designator with
> about
> > > 20
> > > > > > values. I also have about 10 values. What I want to do is have
> the
> > > > > data
> > > > > > listed as
> > > > > >
> > > > > > ROW 1 Data value 1 Data value 2 Data value 3 instead of
> > > > > >
> > > > > > Row 1 Data value1
> > > > > > Data value2
> > > > > > Data value3
> > > > > > Row 2 Data value1
> > > > > >
> > > > > > etc.
> > > > > >
> > > > > > Is there a way to do this that isn't tedious?
> > > > > >
> > > > > > Thanks in advance,
> > > > > > Barb Reinhardt
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Barb,

Yes, you never said that you had a database, but I wanted to be clear in my
description, since pivot tables expect database style data tables. That's
why I asked for an example of the type of data that you had.

HTH,
Bernie
MS Excel MVP

> I never said I had a database. I'll take a look and see what I can glean
> from the link.