Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Creating single lines of data from a multi-column table
Hi folks.
I have a client who wants us to import data held as a grid in Excel into our software as a csv file. The data is held as follows: Job No Prod A Prod B Prod C 1234 6 7 1987 12 1545 12 2 etc To work correctly the csv file needs to create the above as: "1234","Prod A","6" "1234","Prod B","7" "1987","Prod C","12" "1545","Prod B","12" "1545","Prod C","2" In other words: create a line per job/product combination incorporating the Product code into the line. The table also includes jobs without any data. I've searched everywhere for this one - perhaps I am asking the wrong questions! Any help gratefully received. Hywel |
#2
|
|||
|
|||
Are all those things in the third column numbers? If yes, then...
This looks like a job for....Data|Pivottable Add a single row of headers to your data (if you don't have them now). select your data A1:C9999 (through the bottom right corner) Data|pivottable (actually, this menu item changes captions with versions of xl) follow the wizard (just click Next until you get to a dialog that has a Layout button on it) click that Layout button Drag the header for the Job to the Row field. drag the header for the ProdType to the Column field drag the header for the Qty field to the data field If you see "Count of qty" then double click on that one and change it to Sum (sum of qty) click ok and finish Tada! If you want to read more about the pivottable stuff, you may want to look at some links: Debra Dalgleish's pictures at Jon Peltier's site: http://peltiertech.com/Excel/Pivots/pivottables.htm And Debra's own site: http://www.contextures.com/xlPivot01.html John Walkenbach also has some at: http://j-walk.com/ss/excel/files/general.htm (look for Tony Gwynn's Hit Database) Chip Pearson keeps Harald Staff's notes at: http://www.cpearson.com/excel/pivots.htm MS has some at (xl2000 and xl2002): http://office.microsoft.com/downloads/2000/XCrtPiv.aspx http://office.microsoft.com/assistan...lconPT101.aspx hywel wrote: Hi folks. I have a client who wants us to import data held as a grid in Excel into our software as a csv file. The data is held as follows: Job No Prod A Prod B Prod C 1234 6 7 1987 12 1545 12 2 etc To work correctly the csv file needs to create the above as: "1234","Prod A","6" "1234","Prod B","7" "1987","Prod C","12" "1545","Prod B","12" "1545","Prod C","2" In other words: create a line per job/product combination incorporating the Product code into the line. The table also includes jobs without any data. I've searched everywhere for this one - perhaps I am asking the wrong questions! Any help gratefully received. Hywel -- Dave Peterson |
#3
|
|||
|
|||
Thanks, Dave, but the numbers are in 3 separate columns - so job 1234
has got 6 of Prod 6 and 7 of Prod B. Your solution produces: 1234,Sum of Prod A,6 <Blank,Sum of Prod B,7 <Blank, Sum of Prod C,<Blank Whereas I looking to show only non-zero values and the job on each line, like this: 1234,Sum of Prod A,6 1234,Sum of Prod B,7 It's nearly there! Hywel Dave Peterson wrote in message ... Are all those things in the third column numbers? If yes, then... This looks like a job for....Data|Pivottable Add a single row of headers to your data (if you don't have them now). select your data A1:C9999 (through the bottom right corner) Data|pivottable (actually, this menu item changes captions with versions of xl) follow the wizard (just click Next until you get to a dialog that has a Layout button on it) click that Layout button Drag the header for the Job to the Row field. drag the header for the ProdType to the Column field drag the header for the Qty field to the data field If you see "Count of qty" then double click on that one and change it to Sum (sum of qty) click ok and finish Tada! |
#4
|
|||
|
|||
You dragged the header for the prodtype to the Row field.
You can fix it by going back to the wizard (rightclick anywhere in the pivottable and select wizard) Then drag the header for the ProdType to the Column field Or you can just drag the header for that prodtype to the cell directly to its right (same row). Right on top of the Total cell. hywel wrote: Thanks, Dave, but the numbers are in 3 separate columns - so job 1234 has got 6 of Prod 6 and 7 of Prod B. Your solution produces: 1234,Sum of Prod A,6 <Blank,Sum of Prod B,7 <Blank, Sum of Prod C,<Blank Whereas I looking to show only non-zero values and the job on each line, like this: 1234,Sum of Prod A,6 1234,Sum of Prod B,7 It's nearly there! Hywel Dave Peterson wrote in message ... Are all those things in the third column numbers? If yes, then... This looks like a job for....Data|Pivottable Add a single row of headers to your data (if you don't have them now). select your data A1:C9999 (through the bottom right corner) Data|pivottable (actually, this menu item changes captions with versions of xl) follow the wizard (just click Next until you get to a dialog that has a Layout button on it) click that Layout button Drag the header for the Job to the Row field. drag the header for the ProdType to the Column field drag the header for the Qty field to the data field If you see "Count of qty" then double click on that one and change it to Sum (sum of qty) click ok and finish Tada! -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
merge data from multiple columns to single column | Excel Worksheet Functions | |||
I am having problems creating pivot table of data | Charts and Charting in Excel | |||
Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) | |||
Data Table - does it work with DDE links and Stock Tickers? | Excel Worksheet Functions | |||
Data Table - Does it work with DDE links and stock tickers? | Excel Discussion (Misc queries) |