Hi everyone,
This is a question about automating a process, rather than an individual formula or macro problem. I hope it’s not too vague.
Every week I create a report which shows ID’s involved in incidents occurring in certain categories ( see attached spreadsheet for clarification).
I receive the raw data in the format shown on the “Data “ tab. To create the report shown in columns A,B,C,D on the “Summary” tab I filter on category, copying all of the incidents to the “Unique list” then use the advanced filter to show only unique values. I paste these into column B of the “Summary” tab.
I then go back to the “Data” tab, filter on category and then filter on incident and copy all the ID’s to column H of the “Summary” tab. Using the formulas in column I & J I get the Count and unique count and I copy these values into the report in columns C & D. Then I repeat the whole process again for the next unique count.
The formula in Column I is =COUNT(N:N) for count
The formula in Column J is =SUM(IF(FREQUENCY(N:N,N:N)>0,1)) for unique count
This takes a long time because there are lots of incident types, categories and hundreds of lines. There must be a more effective way of doing this but I can’t work it out. The criteria is only that I need to keep the “Data” tab as is and produce the report in columns A to D in the “Summary” tab.
I am using excel 2007. Any help is greatly appreciated.
Regards,
Automation test.xlsx
Ger
Bookmarks