I have a few datasets.
Dataset1 is in the format in an excel file (file 1) below as of Mar2016. There are 3 main product types
(A, B and C) and each product type is categorized into 5 products with data from 2015/January to
2016/March.The headers for the product types (e.g. type A in B2 to G2) are merged cells.
Full dataset 1 (original format)
TypeA TypeB TypeC Amount 2015/January …….. 2015/December 2016/January 2016/February 2016/March 2015/January …….. 2015/December 2016/January 2016/February 2016/March 2015/January …….. 2015/December 2016/January 2016/February 2016/March Product1 11 16 21 10 15 20 14 25 16 13 24 15 3 6 75 2 5 74 Product2 4 6 8 3 5 7 11 46 13 10 45 12 57 2 4 56 1 3 Product3 2 3 6 1 2 5 37 13 8 36 12 7 11 16 28 10 15 27 Product4 6 9 10 5 8 9 11 46 6 10 45 5 46 24 13 45 23 12 Product5 11 26 8 10 25 7 16 24 13 15 23 12 42 11 16 41 10 15
As of Apr2016, I would have another new file (file 2) and the dataset would contain data from
2015/January to 2016/April (with 3 additional columns added for the current month 2016/April
data for the 3 product types)Every month, there would be a new file, containing additional columns for the current month.
Can I have the VBA codes to extract and process the data in the following steps automatically?
1. To extract the data for certain periods only (e.g. from January2016 to March2016) and paste into
a new blank file (in worksheet 1) automaticallyFinal format (worksheet 1)
TypeA TypeB TypeC Amount 2016/January 2016/February 2016/March 2016/January 2016/February 2016/March 2016/January 2016/February 2016/March Product1 10 15 20 13 24 15 2 5 74 Product2 3 5 7 10 45 12 56 1 3 Product3 1 2 5 36 12 7 10 15 27 Product4 5 8 9 10 45 5 45 23 12 Product5 10 25 7 15 23 12 41 10 15
2. After the data are copied into worksheet1, the same table is duplicated in worksheet2 but without
data. Set formula such the value in worksheet2 is equal to the value in worksheet1 divided by 10, e.g.
Sheet!1B2/10. The cell references are the same for the 2 worksheets.Final format (worksheet 2)
TypeA TypeB TypeC Amount 2016/January 2016/February 2016/March 2016/January 2016/February 2016/March 2016/January 2016/February 2016/March Product1 1 1.5 2 1.3 2.4 1.5 0.2 0.5 7.4 Product2 0.3 0.5 0.7 1 4.5 1.2 5.6 0.1 0.3 Product3 0.1 0.2 0.5 3.6 1.2 0.7 1 1.5 2.7 Product4 0.5 0.8 0.9 1 4.5 0.5 4.5 2.3 1.2 Product5 1 2.5 0.7 1.5 2.3 1.2 4.1 1 1.53. To duplicate worksheet 2 in worksheet 3 without formula (copy and paste the data as values)
Cells are formatted to display figures in 1 decimal place.
Final format (worksheet 3)
TypeA TypeB TypeC Amount 2016/January 2016/February 2016/March 2016/January 2016/February 2016/March 2016/January 2016/February 2016/March Product1 1 1.5 2 1.3 2.4 1.5 0.2 0.5 7.4 Product2 0.3 0.5 0.7 1 4.5 1.2 5.6 0.1 0.3 Product3 0.1 0.2 0.5 3.6 1.2 0.7 1 1.5 2.7 Product4 0.5 0.8 0.9 1 4.5 0.5 4.5 2.3 1.2 Product5 1 2.5 0.7 1.5 2.3 1.2 4.1 1 1.5
Every month, there would be a new file, containing additional columns for the current month. The
VBA codes should work for rest of the months and there is no need to write a new set of codes for
each month.As I have a few datasets with quite similar format (slight variations) as Dataset1, preferably I hope
that the VBA codes can allow flexibility in selecting any starting date and end date and any product
number or even any Product type to extract the data, if technically feasible.Or otherwise, I am fine if different set of codes have to be customised and used for each datasets.
Thank you for your help.
Dataset 2
Can I have the VBA codes to extract and process the data in the following steps automatically?Full dataset 2 (original format)
The headers for the product categories (e.g. category X in A5 to A9) are merged cells.Amount TypeA 2015/January …….. 2015/December 2016/January 2016/February 2016/March Cateory X Product1 11 16 21 10 15 20 Product2 4 6 8 3 5 7 Product3 2 3 6 1 2 5 Product4 6 9 10 5 8 9 Product5 11 26 8 10 25 7 Cateory Y Product1 11 16 21 10 15 20 Product2 4 6 8 3 5 7 Product3 2 3 6 1 2 5 Product4 6 9 10 5 8 9 Product5 11 26 8 10 25 7 Cateory Z Product1 11 16 21 10 15 20 Product2 4 6 8 3 5 7 Product3 2 3 6 1 2 5 Product4 6 9 10 5 8 9 Product5 11 26 8 10 25 7
1. To extract the data for certain periods only (e.g. from January2016 to March2016) and paste into a
new blank file (in worksheet 1) automatically. Formatting shall remain (colour of text, highlights, font
size, etc….)Final format (worksheet 1)
Amount 2016/January 2016/February 2016/March Cateory X Product1 10 15 20 Product2 3 5 7 Product3 1 2 5 Product4 5 8 9 Product5 10 25 7 Cateory Y Product1 20 25 30 Product2 13 15 17 Product3 11 12 15 Product4 15 18 19 Product5 20 35 17 Cateory Z Product1 30 35 40 Product2 23 25 27 Product3 21 22 25 Product4 25 28 29 Product5 30 45 272. After the data are copied into worksheet1, the same table is duplicated in worksheet2 but without
data. Set formula such the value in worksheet2 is equal to the value in worksheet1 divided by 10, e.g.
Sheet!1B3/10. The cell references are the same for the 2 worksheets.In worksheet1, data is categorized by Category in Col A and then by Product type in Col B. Data are
arranged according to ascending order of each Category and then list of Products. In 2nd worksheet, to
copy the data from worksheet1 such that data are arranged according to each Product and then list of
Category.Final format (worksheet 2)
Amount 2016/January 2016/February 2016/March Cateory X Product1 10 15 20 Cateory Y Product1 20 25 30 Cateory Z Product1 30 35 40 Cateory X Product2 3 5 7 Cateory Y Product2 13 15 17 Cateory Z Product2 23 25 27 …. ….. ….. ….. …. Cateory X Product5 10 25 7 Cateory Y Product5 20 35 17 Cateory Z Product5 30 45 273. After the data are copied into worksheet2, the same table is duplicated in worksheet3 but without
data. Set formula such the value in worksheet3 is equal to the value in worksheet2 divided by 10 and
round to 0 decimal place, e.g. round(Sheet!1C31/10,0). The cell references are the same for the 2
worksheets.Cells are formatted to display figures in 0 decimal place.
Final format (worksheet 3 & 4)
Amount 2016/January 2016/February 2016/March Cateory X Product1 1 2 2 Cateory Y Product1 2 3 3 Cateory Z Product1 3 4 4 Cateory X Product2 0 1 1 Cateory Y Product2 1 2 2 Cateory Z Product2 2 3 3 …. ….. …. ….. ….. Cateory X Product5 1 3 1 Cateory Y Product5 2 4 2 Cateory Z Product5 3 5 33. To duplicate worksheet 3 in worksheet 4 without formula (copy and paste the data as values). Cells
are formatted to display figures in 0 decimal place.4. To copy entire worksheet 4 and paste as values into worksheet 5.
Final format (worksheet 5) Amount 2016/January 2016/February 2016/March Cateory X Product1 1 2 2 Cateory Y Product1 2 3 3 Cateory Z Product1 3 4 4 Cateory X Product2 0 1 1 Cateory Y Product2 1 2 2 Cateory Z Product2 2 3 3 …. ….. …. ….. ….. Cateory X Product5 1 3 1 Cateory Y Product5 2 4 2 Cateory Z Product5 3 5 35. To copy and paste each of the Product types into next few worksheets and rename the worskheet
name accordingly.If the Product name is too long to fit into worksheet name, to shorten the name based on the max no.
of characters (e.g first few 100 characters).Final format (worksheet 6)-> worksheet name is Product1
Amount 2016/January 2016/February 2016/March Cateory X Product1 1 2 2 Cateory Y Product1 2 3 3 Cateory Z Product1 3 4 4Final format (worksheet 10)-> worksheet name is Product5
Amount 2016/January 2016/February 2016/March Cateory X Product5 1 3 1 Cateory Y Product5 2 4 2 Cateory Z Product5 3 5 3
So what is your question? are you asking for help with this? if so where have you asked? How far have you gotten with it? this is very much doable but A LOT of work is needed and I don't think anyone will be willing to recreate the workbook. It may be worth sending your workbook, PM me for the email address to send it to.