Report
I have a sheet "Sales" which records the transactions.Row 1 is the header.The records starts from row 2 downward. Col A2 store date in "dd/mm/yyyy". Col B2 store invoice number. Col C2 store group. Col D2 store machine. Col E2 store brand Col F2 store quantity. Col G2 store unitprice. I like to generate a report based on month and year against group,machine & brand. Currently I am using the array formula in another sheet "Report" to extract the datas. e.g. in "Report" col B2 the below array formula : Range 2:659 is for the month of January,2004 =SUM(IF(Sales!$C$2:$C$659="Filter",IF(Sales!$D$2:$ D$659="Cat",IF(Sales!$E$2:$E$659="OEM"1,0)))) Currently there are altogether 10 types of machine and 20 types of group but the brand is unfix. Can someone help to do this in vba? I hope I have explain clearly. Regards Michael. -- Michael168 ------------------------------------------------------------------------ Michael168's Profile: http://www.excelforum.com/member.php...nfo&userid=605 View this thread: http://www.excelforum.com/showthread...hreadid=273579 |
Report
It is already done for you. (no macro required) Use a Pivot table.
Data=Pivot Table Reports and Charts. Once created, you can select the date field and group on Month and Year. -- Regards, Tom Ogilvy "Michael168" wrote in message ... I have a sheet "Sales" which records the transactions.Row 1 is the header.The records starts from row 2 downward. Col A2 store date in "dd/mm/yyyy". Col B2 store invoice number. Col C2 store group. Col D2 store machine. Col E2 store brand Col F2 store quantity. Col G2 store unitprice. I like to generate a report based on month and year against group,machine & brand. Currently I am using the array formula in another sheet "Report" to extract the datas. e.g. in "Report" col B2 the below array formula : Range 2:659 is for the month of January,2004 =SUM(IF(Sales!$C$2:$C$659="Filter",IF(Sales!$D$2:$ D$659="Cat",IF(Sales!$E$2: $E$659="OEM"1,0)))) Currently there are altogether 10 types of machine and 20 types of group but the brand is unfix. Can someone help to do this in vba? I hope I have explain clearly. Regards Michael. -- Michael168 ------------------------------------------------------------------------ Michael168's Profile: http://www.excelforum.com/member.php...nfo&userid=605 View this thread: http://www.excelforum.com/showthread...hreadid=273579 |
All times are GMT +1. The time now is 04:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com