I have a database which lists daily entries of production data. In my process, material is input as one code, processed, and broken down into several output codes by composition. Within each production run, there are several metrics such as input code, production time, input weight, output weight, calculated production rate, and the composition of the output product. The composition of the output product can be up to 30 different outputs, most of which present 3-5 output codes per production run (different input codes will have different output codes).
I am trying to standardize a weekly report which summarizes the runs from a time range (1 week).
Right now I have a separate spreadsheet with the date of the week as the reference cell (note: the data is currently updated weekly, so the reference date will be the date of the weekly update. I will soon migrate to a date range).
From there, I use Vlookup to lookup and total the input metrics (input item code, description, production time, production rate, input weight) associated with a particular run. (This is done, basic formula).
From here, I need to extract the composition of the run into the outputs which are >0. In my data table, I have 30 columns for each output code, most of which are blank (not zero). In the weekly report, I want to remove the blank cells to summarize the composition of the output. Once the composition #s are summarized, I will use a vlookup to reverse lookup the Output item code associated with that output component.
I have tried using index/match however my biggest issue is that I have not yet defined a range, as I must first lookup the run entry (using date and input code as criteria).
Later I plan to incorporate the function to a User Form in vba ("Weekly Report", select date range, "Run Report") but that is for a later date. I just want the extraction formula to work first!
For reference -
In the data table:
Criteria 1 Date Range: Col. A
Criteria 2 Item Code: Col. D
Output Codes (Composition) Columns BU to CW
In the Weekly report:
Date Reference Cell: $A$4
Item Code Reference Cell: $A5 to how many runs in the week (5-8)
Composition Start Cell: G6
All help is appreciated. let me know if you have any questions. I will furnish a copy of the table upon request.
I am trying to standardize a weekly report which summarizes the runs from a time range (1 week).
Right now I have a separate spreadsheet with the date of the week as the reference cell (note: the data is currently updated weekly, so the reference date will be the date of the weekly update. I will soon migrate to a date range).
From there, I use Vlookup to lookup and total the input metrics (input item code, description, production time, production rate, input weight) associated with a particular run. (This is done, basic formula).
From here, I need to extract the composition of the run into the outputs which are >0. In my data table, I have 30 columns for each output code, most of which are blank (not zero). In the weekly report, I want to remove the blank cells to summarize the composition of the output. Once the composition #s are summarized, I will use a vlookup to reverse lookup the Output item code associated with that output component.
I have tried using index/match however my biggest issue is that I have not yet defined a range, as I must first lookup the run entry (using date and input code as criteria).
Later I plan to incorporate the function to a User Form in vba ("Weekly Report", select date range, "Run Report") but that is for a later date. I just want the extraction formula to work first!
For reference -
In the data table:
Criteria 1 Date Range: Col. A
Criteria 2 Item Code: Col. D
Output Codes (Composition) Columns BU to CW
In the Weekly report:
Date Reference Cell: $A$4
Item Code Reference Cell: $A5 to how many runs in the week (5-8)
Composition Start Cell: G6
All help is appreciated. let me know if you have any questions. I will furnish a copy of the table upon request.
