Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 23rd 09, 09:16 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 111
Default Summarize Data

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


  #2   Report Post  
Old March 23rd 09, 10:21 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,101
Default Summarize Data

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


  #3   Report Post  
Old March 23rd 09, 11:28 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 111
Default Summarize Data

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
  #4   Report Post  
Old March 23rd 09, 11:35 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
Posts: 806
Default Summarize Data

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 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
  #5   Report Post  
Old March 23rd 09, 11:53 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,101
Default Summarize Data

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Summarize data Chris Excel Discussion (Misc queries) 1 April 28th 09 09:24 PM
Looking for a way to summarize data? Rich Excel Worksheet Functions 0 September 19th 06 06:05 PM
Summarize Data kgsggilbert Excel Discussion (Misc queries) 1 June 8th 05 09:41 PM
Summarize data blstone New Users to Excel 1 May 11th 05 10:18 PM
Summarize Data Set Jim Excel Worksheet Functions 6 April 7th 05 03:46 PM


All times are GMT +1. The time now is 12:53 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017