create Macro – select data, sort by acc no., yr, part no, create P

J

Johnny

Am using Office 2K. Am a novice with macros – have used it by recording
simple macros. Would appreciate any help.

I have imported data into excel with the following columns.
Acc No; Acc Name; Part No; Part Description; Qty; Price; Total; Order Date
(dd-mmm-yy); Invoice Date (dd-mmm-yy); SO No;
The no. of rows in the data can vary from 10 to 10000 or more depending on
the number of years being consolidated. I need help in setting a macro that
will
1. copy the Invoice Date into a column next to the SO No. as an Invoice Year
(yyyy) {and not the date or month }
2. sort all the data by Acc No, InvoiceYear, Part No
3. Create a Pivot Table showing Acc No., Acc Name, Part No and sum of the
qty next to the part no. by years

e.g of Original data as follows:
Acc No Name Part No Part Descr Qty Price Total Or Date Inv Date SO No. Inv
Date
42004 SA 2750-03 Seal 50 0.5 25.00 12-Jan-04 05-Jan-04 208
2004
42004 SA 2750-03 Seal 75 0.5 37.50 12-Feb-05 05-Jan-04 229
2005
31605 Tim 5120-03 Handle 700 1.1 770.0 04-Jan-06 05-Jan-04 320
2006

e.g. of Required data
Year
Acc No Acc Name Part No 2004 2005 2006
31605 Tim 5120-03 700
42004 SA 2750-03 50 75
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top