Insert row when data changes

danwilk

New Member
Joined
Aug 9, 2004
Messages
5
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:
Code:
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]
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

sweater_vests_rock

Well-known Member
Joined
Oct 8, 2004
Messages
1,657
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,723
Messages
5,573,825
Members
412,551
Latest member
soking
Top