I developed an interesting Excel macro this past week and thought I would tell you about it here in case anyone else might find it useful. As the senior quality engineer for the largest integrated nylon plant in the world, among other things, I manage customer complaints made to three of our plants. For me, that means entering about 20 items in an Excel row for each complaint, a list Ive maintained for the past three years; assigning an investigator for each complaint who must complete his or her investigation within 20 days and then issue a formal report (time line, root cause, corrective actions and findings) that is sent to the customer. In the past two years, weve spent millions of dollars addressing issues that led to the complaints so this work is taken very seriously, and eliminating chronic issues is an important goal. Anyway, I have this Excel sheet: the 1st tab contains a row-by-row list of all of our complaints. The 2nd and 3rd tabs contain a summary list where each row highlights a single cause (e.g., Adipic Acid caking), its number of complaints in each year, along with the lbs associated with it, for 2012, 2013 and 2014 by quarter. The 4th tab contains a similar list where each row now organizes the data by customer. This new macro enables me to highlight (place the cursor) a complaint category in tabs 2 and 3 or a customer in tab 4, then press the macro button and within seconds it searches all the complaints in the 1st tab and provides me a list of all the complaints weve had in that category or for a given customer. As an example, that macro allowed me to show that a customer in Japan had entered five complaints since last December, all for the same paperwork issue (mistake on the bill of lading) that had impacted over 1 million lbs of Adipic Acid-obviously, this was a chronic issue with this customer and our corrective actions had not been working. The macro is about 15 lines of code that: clears the space where it writes the information, recognizes the active cell (where the cursor is), searches a particular column in tab 1 by category or by customer, then copies each row that it finds that matches the active cell and writes it into a designated area. The macro takes 10-40 sec depending on the number of items found.
Posted on: Sun, 02 Nov 2014 16:21:29 +0000
Trending Topics
Recently Viewed Topics
© 2015