More complex macro can anyone help

[tahrah]

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

 

[Gary Keramidas]

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.

 

[Debra Dalgleish]

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

 

[Guest]

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

 

[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

 

[tahrah]

Okay all, What I did was create a new column that said Yr 2002, Yr
2003, etc... that did the trick.


Tahrah

 

[Debra Dalgleish]

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.


Tahrah