Summarize Data

[joecrabtree]

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

 

[joel]

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]

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

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

 

[Bernd P]

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,D299),,3)

Sfreq you will find here:
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

 

[joel]

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