Does this mean that FSC is Cost and Tax is tax (er, yeah, I wanted to ask that
question).

I'd add a helper column:
In E1, I'd put: Category
In E2, I'd put this formula:
=IF(OR(C2={"tax","fsc"}),C2,"Cost")
Then drag down the column.

Then select that range (A1:Exxx) and do
data|pivottable
follow the wizard until you get a dialog with a Layout button on it.
click that layout button

Drag account to the row field
drag category to the column field
drag quantity to the data field
drag cost to the data field

If quantity or cost shows Count of, double click on it and change it to Sum of.

And finish the wizard.

Then click and drag the Data grey button one cell to its right.

You'll end up with a couple of extra columns, but it'll be pretty close.

You could hide those columns
or
select all the cells
edit|copy
edit|paste special|values
(now the pivottable is gone and you can do as much as you want to that data.)

If you want to read more about pivottables...

Here are a few 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


ledzepe wrote:

Hi everyone,

I need to reformat a .CSV file that looks like below:

Account Quantity Description Cost
R1234 2 Skids 100.00
R1234 0 FSC 1.50
R1234 0 tax 2.33
R5432 1 box 45.00
R5432 4 bales 50.00
R5432 0 FSC 11.21
R5432 0 tax 10.22

I want the above to look something like below:
ACCOUNT QUANTITY COST FSC tax
R1234 2 100.00 1.50 2.33
R5432 5 95.00 11.21 10.22

I get this .CSV file daily and its usually two thousand plus
rows/lines.

Thanks in advance.

--
ledzepe
------------------------------------------------------------------------
ledzepe's Profile: http://www.excelforum.com/member.php...o&userid=25207
View this thread: http://www.excelforum.com/showthread...hreadid=513688

--

Dave Peterson