All,

I am looking for a way to summarize data. The data format I have is as
follows (Although there could be more of it):

DATE CODE QUANTITY VALUE
01/01/09 ABC 10 1000
01/01/09 ABC 1 100
01/01/09 KDK 4 44
02/01/09 JDJ 2 13
02/01/09 JDJ 33 22

This data is in a worksheet called 'data'. I would like to summarize
this data as follows in a sheet called 'output':

DATE CODE QUANTITY VALUE
01/01/09 ABC 11 1100
01/01/09 KDK 4 44
02/01/09 JDJ 35 35

I.e. summarized by date and code,

Ive tried using pivot tables, but I would like to automate this using
VBA.

Any ideas?

Thanks

Joe

Yo could record a macro while generating the pivot table to get a macro. If
yo want a macro that doesn't generate a pivot table try this

Sub maketable()

RowCount = 1
Do While Range("A" & RowCount) < ""
If Range("A" & RowCount) = Range("A" & (RowCount + 1)) And _
Range("B" & RowCount) = Range("B" & (RowCount + 1)) Then

Range("C" & RowCount) = Range("C" & RowCount) + _
Range("C" & (RowCount + 1))
Range("D" & RowCount) = Range("D" & RowCount) + _
Range("D" & (RowCount + 1))
Rows(RowCount + 1).Delete
Else
RowCount = RowCount + 1
End If
Loop

End Sub


"joecrabtree" wrote:

All,

I am looking for a way to summarize data. The data format I have is as
follows (Although there could be more of it):

DATE CODE QUANTITY VALUE
01/01/09 ABC 10 1000
01/01/09 ABC 1 100
01/01/09 KDK 4 44
02/01/09 JDJ 2 13
02/01/09 JDJ 33 22

This data is in a worksheet called 'data'. I would like to summarize
this data as follows in a sheet called 'output':

DATE CODE QUANTITY VALUE
01/01/09 ABC 11 1100
01/01/09 KDK 4 44
02/01/09 JDJ 35 35

I.e. summarized by date and code,

Ive tried using pivot tables, but I would like to automate this using
VBA.

Any ideas?

Thanks

Joe

On Mar 23, 11:21*am, joel wrote:
Yo could record a macro while generating the pivot table to get a macro. *If
yo want a macro that doesn't generate a pivot table try this

Sub maketable()

RowCount = 1
Do While Range("A" & RowCount) < ""
* *If Range("A" & RowCount) = Range("A" & (RowCount + 1)) And _
* * * Range("B" & RowCount) = Range("B" & (RowCount + 1)) Then

* * * Range("C" & RowCount) = Range("C" & RowCount) + _
* * * * *Range("C" & (RowCount + 1))
* * * Range("D" & RowCount) = Range("D" & RowCount) + _
* * * * *Range("D" & (RowCount + 1))
* * * Rows(RowCount + 1).Delete
* *Else
* * * RowCount = RowCount + 1
* *End If
Loop

End Sub

"joecrabtree" wrote:
All,

I am looking for a way to summarize data. The data format I have is as
follows (Although there could be more of it):

DATE * * * CODE * *QUANTITY * VALUE
01/01/09 * ABC * * * 10 * * * * * * * 1000
01/01/09 * ABC * * * *1 * * * * * * * *100
01/01/09 * KDK * * * 4 * * * * * * * * *44
02/01/09 * JDJ * * * * 2 * * * * * * * * 13
02/01/09 * JDJ * * * * 33 * * * * * * * *22

This data is in a worksheet called 'data'. I would like to summarize
this data as follows in a sheet called 'output':

DATE * * * CODE * *QUANTITY * VALUE
01/01/09 * ABC * * * 11 * * * * * * * 1100
01/01/09 * KDK * * * 4 * * * * * * * * *44
02/01/09 * JDJ * * * * 35 * * * * * * * * 35

I.e. summarized by date and code,

Ive tried using pivot tables, but I would like to automate this using
VBA.

Any ideas?

Thanks

Joe

Thats perfect. Thanks. And if I want the table to appear in another
sheet eg 'ouput' how would i modify the code?

Thanks again

Joe

Hello Joe,

Select an area of 20 rows (can be more or less depending on the count
of your DATE/CODE combinations) and 3 columns and array-enter:
=Sfreq(A2:A99,B2:B99,C2:C99)
Adjacent to that area on the right side array-enter into an area of 1
column and the same count of rows you took earlier:
=INDEX(Sfreq(A2:A99,B2:B99,D2:D99),,3)

Sfreq you will find he
http://www.sulprobil.com/html/sfreq.html

To use this macro:
1. Press ALT + F11
2. Enter a new macro module
3. Copy my function code
4. Go back to your worksheet

Regards,
Bernd

The best way is to copy the data to another worksheet and then make the
summary on the new sheet. I made changes to use two sheets that already
exist in the workbook. Change sheet names as required.

Sub maketable()

Set OldSht = Sheets("Sheet1")
Set NewSht = Sheets("Sheet2")

'Copy old sheet to new sheet
OldSht.Cells.Copy _
Destination:=NewSht.Cells

With NewSht
RowCount = 1
Do While .Range("A" & RowCount) < ""
If .Range("A" & RowCount) = .Range("A" & (RowCount + 1)) And _
.Range("B" & RowCount) = .Range("B" & (RowCount + 1)) Then

.Range("C" & RowCount) = .Range("C" & RowCount) + _
.Range("C" & (RowCount + 1))
.Range("D" & RowCount) = .Range("D" & RowCount) + _
.Range("D" & (RowCount + 1))
.Rows(RowCount + 1).Delete
Else
RowCount = RowCount + 1
End If
Loop
End With
End Sub


"joecrabtree" wrote:

On Mar 23, 11:21 am, joel wrote:
Yo could record a macro while generating the pivot table to get a macro. If
yo want a macro that doesn't generate a pivot table try this

Sub maketable()

RowCount = 1
Do While Range("A" & RowCount) < ""
If Range("A" & RowCount) = Range("A" & (RowCount + 1)) And _
Range("B" & RowCount) = Range("B" & (RowCount + 1)) Then

Range("C" & RowCount) = Range("C" & RowCount) + _
Range("C" & (RowCount + 1))
Range("D" & RowCount) = Range("D" & RowCount) + _
Range("D" & (RowCount + 1))
Rows(RowCount + 1).Delete
Else
RowCount = RowCount + 1
End If
Loop

End Sub

"joecrabtree" wrote:
All,

I am looking for a way to summarize data. The data format I have is as
follows (Although there could be more of it):

DATE CODE QUANTITY VALUE
01/01/09 ABC 10 1000
01/01/09 ABC 1 100
01/01/09 KDK 4 44
02/01/09 JDJ 2 13
02/01/09 JDJ 33 22

This data is in a worksheet called 'data'. I would like to summarize
this data as follows in a sheet called 'output':

DATE CODE QUANTITY VALUE
01/01/09 ABC 11 1100
01/01/09 KDK 4 44
02/01/09 JDJ 35 35

I.e. summarized by date and code,

Ive tried using pivot tables, but I would like to automate this using
VBA.

Any ideas?

Thanks

Joe

Thats perfect. Thanks. And if I want the table to appear in another
sheet eg 'ouput' how would i modify the code?

Thanks again

Joe