create report from this mess?

[Alan]

Hi,
I using Excel 2003 and I have a worksheet that looks like this:

Item Number Item Description Qty Ship Ship $
A VISION PRODUCTIONS
FREIGHT FREIGHT 4 $107.67
SR6400P SR6400P Surround Receiver 1 $461.99
00088944 5 $569.66

ALPHA AUDIO SYSTEMS, INC. - CALIF.
CDR510 CDR510 COMB C/D PLAYER 4 $2,279.96
DCM280P 5 DISC CD CHANGER W/RACK RM 3 $539.97
DN780R DUAL CASSETTE DECK 5 $2,159.95
DNC550R PRO CD RECORDER 7 $3,863.93


Can anyone tell me how I would go about turning this into a data format that
I can run pivot table on? It works for searching for customer product use,
but I want a report that shows a sales summary by customer with sales
numbers only. Any help is greatly appreciated !

 

[Dave Peterson]

It's difficult (for me, anyway) to see the current layout and see the way you
want it.

I'm guessing that "a vision productions" and "alpha audio systems, inc. - calif"
are the customers.

And the rest of the data is laid out in nice columns (A)--although, I'm not
sure what happened with the "0088944" record.

But if you wanted the customer on each row, maybe that would be enough???

I'd insert a new column A.

Then in A2 (headers in row 1???), put:
=B2
(to show "A vision Productions")

Then use the fact that the customer rows are the only rows that don't have a
"qty ship" value.

In A3 put:
=if(c3="",a2,b3)

And drag down.

Select column A and
edit|copy
edit|paste special|values

Filter on column D to get rid of all the rows that have a blank Qty. (It should
eliminate the customer row and the separator row.)

But if I'm wrong with the original layout, then this ain't gonna work!