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
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