Group and SUM


G

Guest

Macro Help

I have in Sheet1 INPUT as under

INPUT
Col-A Col-B

S1 1
S2 1
S3 1
S4 1
S5 1
S1 2
S2 3
S3 4
S4 5
S5 6

I want to use Macro and produce an output in Sheet2 as under. Essentially I
want to group Col-A and see a summation in Col-C

Any help will be greatly appreciated by this Macro Novice.

OUTPUT

Col-A Col-B Col-C Col-D
S1 1 2 3
S2 1 3 4
S3 1 4 5
S4 1 5 6
S5 1 6 7
 
Ad

Advertisements

P

papou

Hi
There is no need for a macro to achieve this.
In sheet2:
place in column A the # S numbers
formula in column B : =SUMIF(Sheet1!A1:A10,A1,Feuil1!B1:B10)
Drag down formula in column B.

HTH
Cordially
Pascal
 
G

Guest

I am aware of this solution. I am learing Macro and wanted to apply to a
relatime application.

I am handling a worksheet that has 40000 Rows & 150 Columns of information.
I want to cull out data from this master worksheet from certain fields. hence
I sought forum help.
 
P

papou

Ok then have a look at this :
Dim LastRowSh1 As Integer, i As Integer
LastRowSh1 = Sheets("Sheet1").Range("A65536").End(xlUp).Row
Sheets("Sheet1").Range("A1:A" & LastRowSh1).AdvancedFilter
Action:=xlFilterCopy, _
CopyToRange:=Sheets("Sheet2").Range("A1"), Unique:=True

Dim MyFormula As String


With Worksheets("Sheet2")
For i = 2 To .Range("A65536").End(xlUp).Row
MyFormula = "=SUMIF(Sheet1!A1:A" & LastRowSh1 & ",A" & i & ",Sheet1!B2:B" &
LastRowSh1 & ")"
..Cells(i, 2).Value = Evaluate(MyFormula)
Next i
End With

HTH
Cordially
Pascal
 
Ad

Advertisements

P

papou

Oops, sorry forgot to mention:
Provided column headers present in sheet1 on first row before data.

Cordially
Pascal
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top