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 |
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 |
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 |
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,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 |
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 |
All times are GMT +1. The time now is 11:06 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com