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

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

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!

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