| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
More 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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More complex macro can anyone help
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. -- Gary "tahrah" wrote in message ups.com... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More complex macro can anyone help
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 DetailGroup. 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 he http://www.contextures.com/xlPivot01.html tahrah wrote: 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 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More complex macro can anyone help
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 -- Thank you, Jennifer "Debra Dalgleish" wrote: 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 DetailGroup. 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 he http://www.contextures.com/xlPivot01.html tahrah wrote: 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 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More complex macro can anyone help
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 Debra Dalgleish wrote: 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 DetailGroup. 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 he http://www.contextures.com/xlPivot01.html tahrah wrote: 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 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More complex macro can anyone help
Okay all, What I did was create a new column that said Yr 2002, Yr
2003, etc... that did the trick. :) Tahrah tahrah wrote: 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 Debra Dalgleish wrote: 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 DetailGroup. 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 he http://www.contextures.com/xlPivot01.html tahrah wrote: 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 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More complex macro can anyone help
Thanks for describing how you solved the problem.
tahrah wrote: Okay all, What I did was create a new column that said Yr 2002, Yr 2003, etc... that did the trick. :) Tahrah tahrah wrote: 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 Debra Dalgleish wrote: 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 DetailGroup. 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 he http://www.contextures.com/xlPivot01.html tahrah wrote: 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 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| complex color fill conditions- if statements or complex formula? | Excel Discussion (Misc queries) | |||
| Problem with a complex Macro | Excel Discussion (Misc queries) | |||
| complex macro?? | Excel Discussion (Misc queries) | |||
| Complex formula or use a macro? | Excel Worksheet Functions | |||
| Help with complex formula or macro or solution | Excel Programming | |||
Hybrid Mode
