Vlookup on multiple choices
 

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

Vlookup on multiple choices
 

I'd probably do this with a Pivot Table and do some evaluations of the Pivot
Table

Let's assume all of your data is in A1:D8. Select that range and

Data | Pivot Table | Finish
Drag Account to "Drop Row Fields Here"
Drag Description to "Drop Column Fields Here"
Drag Cost to "Drop Data Items Here"
Right click on cell A3 if it reads anything but SUM OF COSTand pull down to
Field Settings
Change to Summarize by SUM (if necessary)
This will show your FSC and Tax for each account and you can back calculate
from the Grand Total to get the Cost.

Someone else may have a better idea, but that's my suggestion to get you
part way there..

Barb Reinhardt
Change to Sum
"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

Vlookup on multiple choices
 

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