ExcelBanter - Calculate cell row and column

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Calculate cell row and column (https://www.excelbanter.com/excel-discussion-misc-queries/24430-calculate-cell-row-column.html)

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." wrote in message
...
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." wrote in message
...
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." wrote in message
...
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." wrote in message
...
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." wrote in message
...
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." wrote in message
...
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." wrote in message
...
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." wrote in message
...
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." wrote in message
...
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." wrote in message
...
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." wrote in message
...
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." wrote in message
...
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.