Hello!
I am struggling with what exactly I need to with the data I am gathering, and I'm REALLY hoping I'm not being complicated (of course that would explain the headaches if it is). I need to know if everyone is consuming supplies to make our finished goods. I work in an ERP system called QAD. I've been doing this for 4 years. I've also been trying to monitor the other 3 controllers that work with me since the first of the year, and I'm at the point where I'm kind of tired of spending hours getting behind in other tasks, getting headaches and straining my eyes to go through a spreadsheet that I couldn't get to do what I want all on it's own. I stopped running the macro, but continued to export the files in hopes that I could figure out something easier. I've recently been promoted to team lead, so now I'm required to monitor and track the other 3 controllers in my office and need to find a way sooner rather than later.
Once I run my specific search criteria, I export the information from QAD into Excel, add it to my yearly workbook and run the macro the macro to style the sheet and add in the simple formula in the cells down column H to reference G.
"=IF(OR(G2=G3, G2=G4, G2=G1), "", "x")"
Which is fine if I didn't have various "what if's" parameters. What I have below is just a sample of the 700+ lines that is exported out of QAD for me weekly.
So, when I run the macro, I still have to go back and see, does each ID have the appropriate amount of supplies consumed, or if it didn't require raw supplies, see if the controller type in "regrind" at the end of the work order in the remarks to be seen later. Even with the macro running, it still takes me hours to comb through 700+ line items from week to week.
I'm not sure how else I can do this analysis without a database of sorts. Which is way out of my realm of knowledge, but I have a list of the different products we've produced and the compound or pigment it takes to create each item from the last 4 years, so really I guess anything is possible.
Order ID # of x’s found Location Item Number Item Type Group Lot/Serial Description
1. 439753 345105 X PRODEXT 6010066803206 FINGOOD 156950 345105 SPLINE 7/16 X 15/64 This is Correct, there was nothing found in any of the ID cell's that match 345105.
2. 442103 345100 K-MAT001 VFPVC00104815 COMPOUND FPVC 165149 DIMEX FPVCN80140HU9
3. 442103 345100 PRODEXT 3010900708018 FINGOOD 196200 345100 PW 36IN X 30FT YELLOW Incorrect, this item requires 4 additional lines not one. Even though the formula said it's right, it's not
4. 442103 345101 K-MAT001 COLOR03108003 PIGMENT COLORS 164760 Yellow VPV 61027 Visions
5. 442103 345101 K-MAT001 VFPVC00104815 COMPOUND FPVC 165176 DIMEX FPVCN80140HU9
6. 442103 345101 PRODEXT 3010900708018 FINGOOD 196200 345101 PW 36IN X 30FT YELLOW Incorrect, this item requires 2 additional lines, not one. Again, even though the formula said it's right, it's not
Can I do anything to make my situation more productive and accurate in less time?
Bookmarks