Insert row when data changes

[danwilk]

I have an automated process that downloads data from our AS400 into a spreadsheet and then I have to spend time manually adding blank rows and summing columns. So, how would I create a macro to add a new row when the data in column A changes starting at A2. The new row would need to go before the new data in column A. And also I would like to sum two columns when I add this new row.

Example of my data:

A1   Vendor   Part#        Qty    Cost
A2   00100    part#001    10    1.99
A3   00100    part#002    15    2.49
A4   00200    part#001    10    1.99

I would like to add a blank row before vendor 00200 and sum the qty and the cost.

Thanks for your help it is greatly appreciated. [/code]

 

[sweater_vests_rock]

danwilk.

why don't you just use Excel's subtotals feature? highlight your data, goto Data on the menubar, and select Subtotals...

If an information box pops up, select OK.

At the Subtotals dialog box, the first dropdown list allows you to designate column to watch for change. for you, this would be the Vendor. the second dropdown list allows you to choose a function to use -- pick Sum. In the third box (a scroll-y checkbox), check the box corresponding to "Quantity."

Excel will now automatically create subtotals from the Quantity column anytime that you Vendor column changes. Pretty neat, huh?

Hope this helps. Ben.