I work as an Industrial Engineer for a large company and am in the process of trying to sort through 57K lines of data in 60+ columns to find the solution of why we are experiencing a 1.2 Million dollar variance monthly between 2 facilities. I have found several issues and have my data down to 19K lines. Here is the situation:
I have 19K lines of data in one spreadsheet listing material numbers, order numbers, and quantities for each order for the calendar year. Material numbers in Col. A will be duplicated as many times as there was a production order. Some material numbers may appear 20-30 times. I have another set of data with 275 unique part numbers where I have discovered that this set of numbers had the decimal point in the wrong place in a labor calculation for setup labor. My task now is to provide upper management with the impact of correcting these errors against the General Ledger. I had thought of using a VLOOKUP but I have repeating material numbers in the 19K line data set. I want to find every material number in the 19K data set no matter how many times it occurs, and sum the quantities on all the production orders with that material number then see if that material number is one of the 275 unique material numbers and multiply the $ value attached to that particular material number, of which there are 275, times the sum of all the order quantities for each material number found in the list with 19K entries.
I am an intermediate user JUST learning pivot tables and VLOOKUP this past week {viewed hours of online tutorials} to solve this issue. I have until the 26th of this month, just 15 days to solve the problem of which this is a small part.
Any help would be greatly appreciated. I have put in 70+ hours in the last 4 days and am at the end of my skill set{s}. You'd think someone else in the company would have the skills, and probably do, but resources are slim due to our global cost roll upcoming.
Thanks in advance!
Jeff
Bookmarks