Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table external XLS file source change and GETPIVOTDATA refresh | Excel Discussion (Misc queries) | |||
Need to Add Column to Data in Pivot Table | Charts and Charting in Excel | |||
Sort a Column of Dates in Pivot Table | Excel Worksheet Functions | |||
Pivot Tables, Help? | Excel Discussion (Misc queries) | |||
add a column to a pivot table that would show the difference between 2 other Columns | Excel Worksheet Functions |