Pivot table - Same column Name
 

Hi,

I get a report produced by another application in an excel spreadsheet
and
I am trying to do a pivot table from this report.

In this spreadsheet I have the (partial) following columns with the
headings:

Column a; Column b; Column c; Column d; Column e;
Acc_no; Srce_sys; DQ_error; DQ_error; DQ_error;
- - - -
-
- - - -
-
- - - -
-
- - - -
-

My problem is the following:
If I do a pivot from this information, each column will be a field in
the pivot.

I want columns C, D and E to be just one field, because they all
represent the heading "DQ_error". Is it possible to "merge" these 3
columns so they can be just one field in the pivot.

I know I could do a lot of copying and pasting but in the report there
are about 19 columns with the heading DQ_error so I'm trying to avoid
that.

Thanks


--
Portuga
------------------------------------------------------------------------
Portuga's Profile: http://www.excelforum.com/member.php...fo&userid=6385
View this thread: http://www.excelforum.com/showthread...hreadid=566951

Pivot table - Same column Name
 

one option is to use msquery to access the data and write a union query
that returns just the columns acc_no, srce_sys,dq_error. you need to
rename the dq_error column so their names are unique .. add a number to
the end of each name.. dq_error1, dq_error2 etc.
the query looks like thefollowing if the data is in a csv file called
'data'.

SELECT data.Acc_no, data.Srce_sys, data.DQ_error1
FROM data.csv data
union all
SELECT data.Acc_no, data.Srce_sys, data.DQ_error2
FROM data.csv data
union all
SELECT data.Acc_no, data.Srce_sys, data.DQ_error3
FROM data.csv data
<etc up to column 19

I hope someone has an easier suggestion for you.
regards..


--
steven1001
------------------------------------------------------------------------
steven1001's Profile: http://www.excelforum.com/member.php...o&userid=30757
View this thread: http://www.excelforum.com/showthread...hreadid=566951

Pivot table - Same column Name
 

If the columns contain numbers, you could insert a new column, titled
DQ_errors, and sum the data in all the DQ_error columns.
Then, use the new column in the pivot table.

Portuga wrote:
Hi,

I get a report produced by another application in an excel spreadsheet
and
I am trying to do a pivot table from this report.

In this spreadsheet I have the (partial) following columns with the
headings:

Column a; Column b; Column c; Column d; Column e;
Acc_no; Srce_sys; DQ_error; DQ_error; DQ_error;
- - - -
-
- - - -
-
- - - -
-
- - - -
-

My problem is the following:
If I do a pivot from this information, each column will be a field in
the pivot.

I want columns C, D and E to be just one field, because they all
represent the heading "DQ_error". Is it possible to "merge" these 3
columns so they can be just one field in the pivot.

I know I could do a lot of copying and pasting but in the report there
are about 19 columns with the heading DQ_error so I'm trying to avoid
that.

Thanks




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

Pivot table - Same column Name
 

Thanks

Unfortunatelly the column values are not numerical.





Debra Dalgleish Wrote:
If the columns contain numbers, you could insert a new column, titled
DQ_errors, and sum the data in all the DQ_error columns.
Then, use the new column in the pivot table.

Portuga wrote:
Hi,

I get a report produced by another application in an excel
spreadsheet
and
I am trying to do a pivot table from this report.

In this spreadsheet I have the (partial) following columns with the
headings:

Column a; Column b; Column c; Column d; Column e;
Acc_no; Srce_sys; DQ_error; DQ_error;
DQ_error;
- - - -
-
- - - -
-
- - - -
-
- - - -
-

My problem is the following:
If I do a pivot from this information, each column will be a field
in
the pivot.

I want columns C, D and E to be just one field, because they all
represent the heading "DQ_error". Is it possible to "merge" these 3
columns so they can be just one field in the pivot.

I know I could do a lot of copying and pasting but in the report
there
are about 19 columns with the heading DQ_error so I'm trying to
avoid
that.

Thanks




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


--
Portuga
------------------------------------------------------------------------
Portuga's Profile: http://www.excelforum.com/member.php...fo&userid=6385
View this thread: http://www.excelforum.com/showthread...hreadid=566951

Pivot table - Same column Name
 

I there's just one DQ_error per row, you could concatenate the values:

=C2&D2&E2

Portuga wrote:
Thanks

Unfortunatelly the column values are not numerical.





Debra Dalgleish Wrote:

If the columns contain numbers, you could insert a new column, titled
DQ_errors, and sum the data in all the DQ_error columns.
Then, use the new column in the pivot table.

Portuga wrote:

Hi,

I get a report produced by another application in an excel

spreadsheet

and
I am trying to do a pivot table from this report.

In this spreadsheet I have the (partial) following columns with the
headings:

Column a; Column b; Column c; Column d; Column e;
Acc_no; Srce_sys; DQ_error; DQ_error;

DQ_error;

- - - -
-
- - - -
-
- - - -
-
- - - -
-

My problem is the following:
If I do a pivot from this information, each column will be a field

in

the pivot.

I want columns C, D and E to be just one field, because they all
represent the heading "DQ_error". Is it possible to "merge" these 3
columns so they can be just one field in the pivot.

I know I could do a lot of copying and pasting but in the report

there

are about 19 columns with the heading DQ_error so I'm trying to

avoid

that.

Thanks




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





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