VBScript to insert new row and add SUM() formula?

Question

ello!

I have a spreadsheet that lists Country, Invoice Numbers, and their price, set up like this:

	A	B	C	D
1	Country	Invoice Number	Price	Total Invoice Price
2	USA	1142	2.3
3	USA	1142	2.41
4	USA	1142	17.00
5	Mexico	1142	4.00
6	Mexico	1142	6.00
7	France	2211	26.00
8	France	2113	29.00
9	France	255	16.00
10	Italy	255	15.00
11	Italy	255	5.00
12	Italy	255	13.00

I would like to have a script go through column A and B, and compare the two.  Every time there is a difference in Country or Invoice number from the cell above, I would like a row inserted, and the invoice values of those "grouped together" to be added to "Total Invoice Price".  So it'll then look like this (with formula for easier explanation I hope):

	A	B	C	D
1	Country	Invoice Number	Price	Total Invoice Price
2	USA	1142	2.3
3	USA	1142	2.41
4	USA	1142	20	=SUM(F5:F7)
5
6	Mexico	1142	23
7	Mexico	1142	1	=SUM(F9:F10)
8
9	France	2211	18	=SUM(F12)
10
11	France	2113	16	=SUM(F14)
12
13	France	255	10	=SUM(F16)
14
15	Italy	255	12
16	Italy	255	30
17	Italy	255	25	=SUM(F18:F20)

Does that make sense?  Goes down column A and B, compares those values from the cell above, if both are the same, move on...if one differs, add a new row and add the SUM() of the block of column C from that grouping.

Sorry if I explained this in a convoluted way ...please let me know if you'd like a better explanation or clarification.

Thank you!

Answer 1

Hi,

you can do it using a Pivot Table.

1. Row Field >>> Country and Invoice Number

and

2. Data Field >> Price

see the link...Pivot Table

Answer 2

You are welcome, glad I could help.

Answer 3

O_O

So THAT'S what a Pivot Table is. 

Thank you so very much, this is brilliant.