excel sorting/filtering/and counting data automatically using macro

Question

so I have an excel datasheet (sheet1) with a few columns.

company | product code | date | name |

and a few rows that fill all the above details.

e.g.

company 1 | adar1 | 01/sep/14 | title1 |
company 2 | adar2 | 04/sep/14 | title2 |
company 3 | adar1 | 09/sep/14 | title3 |
company 4 | adar3 | 10/oct/14 | title4 |
company 1 | adar2 | 18/oct/14 | title5 |
company 2 | adar1 | 04/nov/14 | title6 |
company 3 | adar3 | 09/nov/14 | title7 |
company 4 | adar3 | 01/nov/14 | title8 |

etc.

As you see the company names differ. the product names differ. dates vary and name/titles we do not care about.

what I want to do is to apply some sort of filter that can do the following:

PER MONTH, to count the same product names PER COMPANY, and create rows/columns on a new sheet (sheet2) with the following format:

company | quantity | product code | date in specific format below |
company 1    n           adar1       201409
company 1    n           adar2       201409
company 2    n           adar1       201409
company 1    n           adar2       201409
company 1    n           adar1       201410
company 1    n           adar2       201411

etc etc

meaning company 1 used n number of adar1 product, n number of adar2 product on september 2014 (201409) company 2 used n number of adar1 and n of adar3 on september 2014 and october 2014 etc,

I believe you get the point.

I can do all the above with pivot tables but I would like to know whether there is a macro or some function/code that can do all the above automatically on sheet2 upon entering new values on sheet1.

Thank You.

If you can do it with pivot tables why not simply add a macro to refresh the pivot table upon entering values on sheet1? See msdn.microsoft.com/en-us/library/office/… — AnalystCave.com
because after sorting all my data using a pivot table, I still have to manually copy the pivot table's results to sheet 2 using the exact above format. Sheet2 cannot be a pivot table. Without the pivot table I used to spend 8 hours sorting and filtering data manually. Using pivot table I dropped the time spent to 2 hours. I was wondering if some excel/VBA guru in here could help me do this with one click!! — giannisc

AnalystCave.com AnalystCave.com · Accepted Answer · 2014-09-26T12:43:30

I would go about this with a SQL something like this:

SELECT * FROM (SELECT s.company, COUNT(s.company) as quantity, s.product_code, "20"&right(s.date,2)&iif(right(left(s.date,6),3)="jan","01",iif(right(left(s.date,6),3)="feb","02","03"))&left(s.date,2) as new_date FROM [Sheet1$] as s GROUP BY s.company,s.product_code,s.date) ORDER BY new_date DESC

To use it see Data->External Data->From other sources->Microsoft Query or use my SQL AddIn: http://blog.tkacprow.pl/?page_id=130.

The SQL above is still missing a mapping for months mar-dec. I am sure you will be able to add them by yourself.

NOTE1: To refresh this when values are updated you can add a macro button that refreshes the table or override Worksheet Change event in VBA.

excel sorting/filtering/and counting data automatically using macro

1 Answers