 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / January 2007More complex macro can anyone help
With the help of the good people on this group I've been able to create about 8 macros for my company. However, now I'm being asked to create a macro that is way out of my league. Maybe this task is too big for a macro. I have the following columns in my spreadsheet: A=Part Number B=Customer C=Sales Order Number D=Date E=Quantity F=Unit Price G=Extended Price H=Product Type I need to take the last three years and separate the rows by customer and then by part number, showing the total dollars booked for each customer/part number for each quarter. So my new spreadsheet would to look something like this: QTR 1 QTR 2 QTR3 QTR4 CUSTOMER #1 PART #1 $ $ $ $ PART #2 $ $ $ $ PART #3 $ $ $ $ PART #4 $ $ $ $ PART #5 $ $ $ $ PART #6 $ $ $ $ CUSTOMER #2 PART #1 $ $ $ $ PART #2 $ $ $ $ PART #3 $ $ $ $ PART #4 $ $ $ $ PART #5 $ $ $ $ PART #6 $ $ $ $ CUSTOMER #3 PART #1 $ $ $ $ PART #2 $ $ $ $ PART #3 $ $ $ $ PART #4 $ $ $ $ PART #5 $ $ $ $ PART #6 $ $ $ $ CUSTOMER=Actual Customer Name PART NUMBER=Actual Part Number Some customers would have 2-3 parts while others might have 20. Is this possible with a macro and is there anyone out there who could help me with this? I can do a basic sort. But figuring out how to get it to total up the separate part numbers for each quarter and place this new information all on another page has got me LOST! I appreciate any help. Regards, Tahrah not saying excel couldn't do it, but it would be better suited for a database program. import the date and create a report which shouldn't be too hard at all.  SignatureGary > With the help of the good people on this group I've been able to create > about 8 macros for my company. However, now I'm being asked to create [quoted text clipped - 73 lines] > Regards, > Tahrah You could use a pivot table to summarize the data. Add Date, Customer and PartNumber to the row area, and ExtendedPrice to the data area, where it will appear as Sum of Extended Price. Right-click on the Date field button, and choose Group and Show Detail>Group. Choose to Group by Quarters, or by Quarters and Years Then, move the Date field(s) to the Column Area. There are pivot table instructions and links here: http://www.contextures.com/xlPivot01.html > With the help of the good people on this group I've been able to create > about 8 macros for my company. However, now I'm being asked to create [quoted text clipped - 73 lines] > Regards, > Tahrah SignatureDebra Dalgleish Contextures http://www.contextures.com/tiptech.html 2 words: PIVOT TABLE I think it is one of the greatest tools in excel. Takes 5 minutes to learn and it will only take 30 sec. to put together the spreadsheet you are asking for. This is exactly what a pivot table was designed for. Have fun! Here are some instructions http://www.contextures.com/xlPivot01.html SignatureThank you, Jennifer > You could use a pivot table to summarize the data. > Add Date, Customer and PartNumber to the row area, and ExtendedPrice to [quoted text clipped - 86 lines] > > Regards, > > Tahrah Debra and all - The pivot table worked great except I cannot figure out how to get it to group. I tried to right click and group by quarters and group by years, but when I do quarters, it's only giving me "4" quarters even though this is a 4 year span. For years, it's giving me every year from 2002 to 2006, but for quarters, it just says Q1, Q2, Q3, Q4 even though it should be Q1-2002, Q2-2002, Q3-2002, Q4-2002, Q1-2005, Q2-2005, etc... Any ideas? Regards, Tahrah > You could use a pivot table to summarize the data. > Add Date, Customer and PartNumber to the row area, and ExtendedPrice to [quoted text clipped - 91 lines] > Contextures > http://www.contextures.com/tiptech.html Okay all, What I did was create a new column that said Yr 2002, Yr 2003, etc... that did the trick. :) Tahrah > Debra and all - The pivot table worked great except I cannot figure out > how to get it to group. I tried to right click and group by quarters [quoted text clipped - 104 lines] > > Contextures > > http://www.contextures.com/tiptech.html Thanks for describing how you solved the problem. > Okay all, What I did was create a new column that said Yr 2002, Yr > 2003, etc... that did the trick. [quoted text clipped - 110 lines] >>>Contextures >>>http://www.contextures.com/tiptech.html SignatureDebra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.