Group / Sum two columns

[papasmurfuo9]

Hi

quite difficult to explain in the subject, but this is what im after

i have two columns, A and B on sheet1

Column A Column B
1 22.30
1 13.40
1 55.20
3 8.69
3 6.58
4 5.55
4 12.55
4 33.55

what i would like is some vba to convert the above, onto sheet2 as follows

Column A Column B
1 90.90 (so a sum of all the values relating to 1 in column A)
3 15.27
4 46.10


the range on sheet 1, could go up to row A1:B1000

many thanks in advance

[Pete_UK]

Why use VBA? You can do it quite easily using a SUMIF formula in B1 of Sheet2, like this:

=SUMIF(Sheet1!A:A,A1,Sheet1!B:B)

then copy down.

Hope this helps.

Pete

[papasmurfuo9]

Hi Pete, thanks for the reply

the requestor, has requested that they simply press a button, and sheet2 is created, with the variable range etc. is it possible to put your method into vba with the same end result? thanks!

[millz]

You can try this:

Please Login or Register  to view this content.

Amended slightly from a previous code

[papasmurfuo9]

i get a invalid bracketing error message on this line

rs.Open s, cn


thank you

[papasmurfuo9]

i get a invalid bracketing error message on this line

rs.Open s, cn

i changed the brackets to ( from [ and, then the sheet it creates only sums the first number from column a

thank you

[millz]

How does your data structure looks like? There are blanks inbetween rows? Post a sample workbook with de-sensitized data if possible.

[papasmurfuo9]

Hi

i managed to solve it, but copying column A into sheet 2, removing duplicates, then applying the sum if,,
thanks anyway, TA