chriscorpion786
Board Regular
- Joined
- Apr 3, 2011
- Messages
- 112
- Office Version
- 365
- Platform
- Windows
[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Dear All,
I have data as per below and I want to make a summary in VBA, like the table on the right side in bold, which shows by Name,
by category the values sold and it should be by month, but my dates are as per below, so if i have January then it should be 31 columns. I have tried 4 nested loops, one to loop for all the names, and within this another loop to go through categories and another loop to go from 1 to 31 columns, but the macro takes too long.
Is there an optimization code that you could help with and how to go about it. I have dates running across columns for each day and I need to make a report for each month similar to the right table. VBA help required please.
[TABLE="width: 898"]
<colgroup><col><col><col><col span="4"><col><col span="4"></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Category[/TD]
[TD]Price[/TD]
[TD]1/1/2018[/TD]
[TD]1/2/2018[/TD]
[TD]1/3/2018[/TD]
[TD]1/4/2018[/TD]
[TD][/TD]
[TD] Name [/TD]
[TD] Chocolate [/TD]
[TD] Pastries [/TD]
[TD] Cakes [/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD]Pastries[/TD]
[TD="align: right"]8[/TD]
[TD]22[/TD]
[TD]11[/TD]
[TD]22[/TD]
[TD]11[/TD]
[TD][/TD]
[TD] Chris [/TD]
[TD] 280.0[/TD]
[TD] 528.0[/TD]
[TD] 608.0[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]Pastries[/TD]
[TD="align: right"]8[/TD]
[TD]27[/TD]
[TD]29[/TD]
[TD]23[/TD]
[TD]13[/TD]
[TD][/TD]
[TD] Mike [/TD]
[TD] 756.0[/TD]
[TD] 3,043.0[/TD]
[TD] 441.0[/TD]
[/TR]
[TR]
[TD]Alan[/TD]
[TD]Cakes[/TD]
[TD="align: right"]5[/TD]
[TD]30[/TD]
[TD]11[/TD]
[TD]20[/TD]
[TD]29[/TD]
[TD][/TD]
[TD] Alan [/TD]
[TD] 816.0[/TD]
[TD] 504.0[/TD]
[TD] 1,138.0[/TD]
[/TR]
[TR]
[TD]Davis[/TD]
[TD]Cakes[/TD]
[TD="align: right"]6[/TD]
[TD]30[/TD]
[TD]22[/TD]
[TD]20[/TD]
[TD]29[/TD]
[TD][/TD]
[TD] Davis [/TD]
[TD] 720.0[/TD]
[TD] 370.0[/TD]
[TD] 606.0[/TD]
[/TR]
[TR]
[TD]Eva[/TD]
[TD]Chocolate[/TD]
[TD="align: right"]5[/TD]
[TD]29[/TD]
[TD]23[/TD]
[TD]11[/TD]
[TD]27[/TD]
[TD][/TD]
[TD] Eva [/TD]
[TD] 982.0[/TD]
[TD] 525.0[/TD]
[TD] 816.0[/TD]
[/TR]
[TR]
[TD]Alan[/TD]
[TD]Chocolate[/TD]
[TD="align: right"]8[/TD]
[TD]20[/TD]
[TD]28[/TD]
[TD]25[/TD]
[TD]29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]Pastries[/TD]
[TD="align: right"]9[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]14[/TD]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]Pastries[/TD]
[TD="align: right"]8[/TD]
[TD]20[/TD]
[TD]28[/TD]
[TD]25[/TD]
[TD]29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Eva[/TD]
[TD]Cakes[/TD]
[TD="align: right"]8[/TD]
[TD]20[/TD]
[TD]28[/TD]
[TD]25[/TD]
[TD]29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alan[/TD]
[TD]Pastries[/TD]
[TD="align: right"]6[/TD]
[TD]17[/TD]
[TD]30[/TD]
[TD]23[/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]Pastries[/TD]
[TD="align: right"]6[/TD]
[TD]15[/TD]
[TD]25[/TD]
[TD]15[/TD]
[TD]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Appreciate the help.
Mustafa
<tbody>[TR]
[TD="width: 64"]Dear All,
I have data as per below and I want to make a summary in VBA, like the table on the right side in bold, which shows by Name,
by category the values sold and it should be by month, but my dates are as per below, so if i have January then it should be 31 columns. I have tried 4 nested loops, one to loop for all the names, and within this another loop to go through categories and another loop to go from 1 to 31 columns, but the macro takes too long.
Is there an optimization code that you could help with and how to go about it. I have dates running across columns for each day and I need to make a report for each month similar to the right table. VBA help required please.
[TABLE="width: 898"]
<colgroup><col><col><col><col span="4"><col><col span="4"></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Category[/TD]
[TD]Price[/TD]
[TD]1/1/2018[/TD]
[TD]1/2/2018[/TD]
[TD]1/3/2018[/TD]
[TD]1/4/2018[/TD]
[TD][/TD]
[TD] Name [/TD]
[TD] Chocolate [/TD]
[TD] Pastries [/TD]
[TD] Cakes [/TD]
[/TR]
[TR]
[TD]Chris[/TD]
[TD]Pastries[/TD]
[TD="align: right"]8[/TD]
[TD]22[/TD]
[TD]11[/TD]
[TD]22[/TD]
[TD]11[/TD]
[TD][/TD]
[TD] Chris [/TD]
[TD] 280.0[/TD]
[TD] 528.0[/TD]
[TD] 608.0[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]Pastries[/TD]
[TD="align: right"]8[/TD]
[TD]27[/TD]
[TD]29[/TD]
[TD]23[/TD]
[TD]13[/TD]
[TD][/TD]
[TD] Mike [/TD]
[TD] 756.0[/TD]
[TD] 3,043.0[/TD]
[TD] 441.0[/TD]
[/TR]
[TR]
[TD]Alan[/TD]
[TD]Cakes[/TD]
[TD="align: right"]5[/TD]
[TD]30[/TD]
[TD]11[/TD]
[TD]20[/TD]
[TD]29[/TD]
[TD][/TD]
[TD] Alan [/TD]
[TD] 816.0[/TD]
[TD] 504.0[/TD]
[TD] 1,138.0[/TD]
[/TR]
[TR]
[TD]Davis[/TD]
[TD]Cakes[/TD]
[TD="align: right"]6[/TD]
[TD]30[/TD]
[TD]22[/TD]
[TD]20[/TD]
[TD]29[/TD]
[TD][/TD]
[TD] Davis [/TD]
[TD] 720.0[/TD]
[TD] 370.0[/TD]
[TD] 606.0[/TD]
[/TR]
[TR]
[TD]Eva[/TD]
[TD]Chocolate[/TD]
[TD="align: right"]5[/TD]
[TD]29[/TD]
[TD]23[/TD]
[TD]11[/TD]
[TD]27[/TD]
[TD][/TD]
[TD] Eva [/TD]
[TD] 982.0[/TD]
[TD] 525.0[/TD]
[TD] 816.0[/TD]
[/TR]
[TR]
[TD]Alan[/TD]
[TD]Chocolate[/TD]
[TD="align: right"]8[/TD]
[TD]20[/TD]
[TD]28[/TD]
[TD]25[/TD]
[TD]29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]Pastries[/TD]
[TD="align: right"]9[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]14[/TD]
[TD]18[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]Pastries[/TD]
[TD="align: right"]8[/TD]
[TD]20[/TD]
[TD]28[/TD]
[TD]25[/TD]
[TD]29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Eva[/TD]
[TD]Cakes[/TD]
[TD="align: right"]8[/TD]
[TD]20[/TD]
[TD]28[/TD]
[TD]25[/TD]
[TD]29[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alan[/TD]
[TD]Pastries[/TD]
[TD="align: right"]6[/TD]
[TD]17[/TD]
[TD]30[/TD]
[TD]23[/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]Pastries[/TD]
[TD="align: right"]6[/TD]
[TD]15[/TD]
[TD]25[/TD]
[TD]15[/TD]
[TD]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Appreciate the help.
Mustafa