I have a report that I can get out of my accounting that I need to
manipulate to get the data I want.

the report is a sales report listed by customer, and I want to see the
top 20 purchasers of a product that I run this on.

the report looks like this

Customer name, - blank cells all the way across
invoice #, , date,customerid-name, po#, productcat/name, quantity,
unit, price, unit, dollars
as many lines of this as are in the date range the report was run on.
, , , customer name, , , , total $, ,
then it starts over

I need to sum the product for each customer - so I can get the total
of this product.
so how do I do this.


the product quantity is column h there will be for 1 - 30 numbers and
then 2 blank cells
where the last row of this customers report is and the first row the
the next customer report begins.

can I somehow make a macro that scans down column h for 2 and no more
blank cells then from the first one scan up till the next blank cell -
then insert a =sum() with the cell range that it found

If you need a picture of the layout to better understand what the
spreadsheet looks I uploaded a screen shot here http://www.litwiller.net/images/excel.gif

You can pretty much build that layout with Data=Subtotals applied against a
sorted table of data.

You might have to add code to put in the extra line.

--
Regaqrds,
Tom Ogilvy


" wrote:

I have a report that I can get out of my accounting that I need to
manipulate to get the data I want.

the report is a sales report listed by customer, and I want to see the
top 20 purchasers of a product that I run this on.

the report looks like this

Customer name, - blank cells all the way across
invoice #, , date,customerid-name, po#, productcat/name, quantity,
unit, price, unit, dollars
as many lines of this as are in the date range the report was run on.
, , , customer name, , , , total $, ,
then it starts over

I need to sum the product for each customer - so I can get the total
of this product.
so how do I do this.


the product quantity is column h there will be for 1 - 30 numbers and
then 2 blank cells
where the last row of this customers report is and the first row the
the next customer report begins.

can I somehow make a macro that scans down column h for 2 and no more
blank cells then from the first one scan up till the next blank cell -
then insert a =sum() with the cell range that it found

If you need a picture of the layout to better understand what the
spreadsheet looks I uploaded a screen shot here http://www.litwiller.net/images/excel.gif

Ok, I tried Data=Subtotals - I'll have to play with that a bunch -to
see if I can get anything I can use out of it.



On Jun 15, 1:19 pm, Tom Ogilvy
wrote:
You can pretty much build that layout with Data=Subtotals applied against a
sorted table of data.

You might have to add code to put in the extra line.

--
Regaqrds,
Tom Ogilvy

" wrote:
I have a report that I can get out of my accounting that I need to
manipulate to get the data I want.

the report is a sales report listed by customer, and I want to see the
top 20 purchasers of a product that I run this on.

the report looks like this

Customer name, - blank cells all the way across
invoice #, , date,customerid-name, po#, productcat/name, quantity,
unit, price, unit, dollars
as many lines of this as are in the date range the report was run on.
, , , customer name, , , , total $, ,
then it starts over

I need to sum the product for each customer - so I can get the total
of this product.
so how do I do this.

the product quantity is column h there will be for 1 - 30 numbers and
then 2 blank cells
where the last row of this customers report is and the first row the
the next customer report begins.

can I somehow make a macro that scans down column h for 2 and no more
blank cells then from the first one scan up till the next blank cell -
then insert a =sum() with the cell range that it found

If you need a picture of the layout to better understand what the
spreadsheet looks I uploaded a screen shot herehttp://www.litwiller.net/images/excel.gif