+ Reply to Thread
Results 1 to 4 of 4

Sorting Through 19,000+ Lines to Solve a $1.2 Million Monthly Labor/Materials Variance

  1. #1
    Registered User
    Join Date
    10-11-2012
    Location
    Alabama
    MS-Off Ver
    Excel 2007
    Posts
    2

    Sorting Through 19,000+ Lines to Solve a $1.2 Million Monthly Labor/Materials Variance

    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

  2. #2
    Registered User
    Join Date
    10-11-2012
    Location
    Alabama
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Sorting Through 19,000+ Lines to Solve a $1.2 Million Monthly Labor/Materials Variance

    I have attached a smallportion of the workbook. I want to search my entire DATA tab, column A against the part number list on tab "275 unique material numbers" column A as well. If the part number form the tab "275..." is found in the DATA tab col. A {I have highlighted in yellow the appropriate cells}, I want to total the order quantities, DATA tab, cells D2 and D3, and then multiply that sum {208 pieces} times the amount of variance created {tab "275...} cell H2 {$44.06} and place the result {208 pieces x 44.06 = $9,164.48} in cell I2 on the "275.....} tab OR where ever is possible as long at the offending part number is next to it.

    Thanks!
    Attached Files Attached Files
    Last edited by Farrar123; 10-12-2012 at 01:09 AM.

  3. #3
    Registered User
    Join Date
    09-02-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    77

    Re: Sorting Through 19,000+ Lines to Solve a $1.2 Million Monthly Labor/Materials Variance

    Hi,

    welcome to the forum i took a shot at you problem you could try this

    =SUMIF(Data!$A$2:$A$1154,'275 Unique Material Numbers'!A2,Data!$D$2:$D$1154)*H2
    Regards,
    Hyperdude

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,449

    Re: Sorting Through 19,000+ Lines to Solve a $1.2 Million Monthly Labor/Materials Variance

    Hi Jeff,

    It looks like you need a Pivot Table from your data on sheet1 and then a VLookup next to it. See the example. Then I believe you want to simply sum the VLookup column to get a total. I do find your list of 275 doesn't include all the products. Are there lots of products that need to be left out of the data? If so you might need an Advanced Filter of the large data set using the list of 275. This would then solve all those N/A.

    See the attached with a Pivot Table and VLookup method that would work if you had matches for all those extra parts. Also see an intermediate step of using an Advanced Filter of you large set using the 275 parts as a criteria for the filter. This gives a new set of data (to the right in the example) that you can then do your Pivot Table and VLookup on.

    See the attached. Do an Advanced Filter using the 275 the Pivot and VLookup and I think that does what you want.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1