PDA

View Full Version : Solved: Quantity at Operation based on In/Out marker



CodeMakr
01-09-2007, 05:08 PM
I need to get the quantity currently at a workcenter operation. The database where the raw data is grabbed, currently shows transactions in and out of the workcenter (RELB), but the nomenclature doesn't make it easy/intuitive to get the number from the raw data. Take a look at the attachment. Any help is greatly appreciated : pray2:

XLGibbs
01-09-2007, 06:26 PM
SO what data precisely do you need to get out of that? And is that the exact format that is in question?

I assume the problem is "netting out" from the To/From transactions appearing in One column?

And the line at the bottom is the desired result you want?

XLGibbs
01-09-2007, 06:27 PM
Edit: I see you have before and after...how does the raw data get to you? and approx how many rows of data do you need to address with the real file?

I assume you want a macro to run which will kind of clean it up and produce the "after"?

CodeMakr
01-10-2007, 08:32 AM
There is never more than 50 rows of finished data. The data is gathered via access, and then exported as a text file. This text file data is added to other data to get workcenter loading. Currently, I have to manually figure/enter the data into my spreadsheet to get 1 line of data per purchase order number and PO line number with a sum of qty that is currently still at the operation (vs. passed through).
:dunno

CodeMakr
01-10-2007, 08:33 AM
Edit: total rows of raw data before compiled ranges from 100-200.

XLGibbs
01-10-2007, 08:41 AM
So what are the manual steps that need to be tied to a macro?

What exactly is the layout of the rows to evaluate and re-structure into the 1 line of data (is it 1 line per xxxx number of yyyy potential categories, or is it always xxxx number of lines to total into 1 summary line?)

CodeMakr
01-10-2007, 08:54 AM
Each purchase order & line number combination is evaluated to match in/out transactions. Zero quantity means there is none AT the particular workcenter (RELB). In most cases there is only 1 in and 1 out...meaning the quantity has moved through the workcenter. The issue is large blanket purchase orders with multiple deliveries. For example, an annual blanket order for weekly deliveries, is going to show all in/out activity for the last xx months. From that data, I need to find out what qty are actually still at the operation (versus everything that has moved through the workcenter....which is what the raw data gives).

Make sense???

XLGibbs
01-10-2007, 09:26 AM
Yes, I follow all that, seemed that was the case from the sample set of data

I am trying to determine what your manual steps to determine that result might be, so I can try to automate it.

CodeMakr
01-10-2007, 09:44 AM
I first sort by purchase order number and purchase order line number so all transactions for the same part number/PO/line combination are together.

An "into operation" transactions has "--->" in the FromOp and "RELB" in the ToOp, and an "out of operation" transaction has "RELB" in the FromOp and "--->" in the ToOp.

I have a formula to look at the FromOp to qualify if it is "--->", list quantity, else list qty * -1. That gives me negative qty for each "out of operation" transaction. If the qty sub-total for each of these groups of PO/lines is greater than zero, I want to put the part number in colA, text "REL" in colB, PO# (dash) line# in colC, date formula in colD, and sum of qty at operation in colE.....which I am currently doing manually. :doh:

XLGibbs
01-10-2007, 09:49 AM
Okay, so you get a data set

the following appear to be automate-able
1.Sorting it.
2.Applying logic similar to your formula so that the the net of in/out can be calculated.
3. Formatting as you indicated.

Got a sanitized version of the raw data from before you sort? i can play around with it...seems like another interesting job to tackle.

CodeMakr
01-10-2007, 10:38 AM
Here is an updated example. 3 sheets. first is raw data imported from access query. second is how I separate raw data to get open qty. third is finished data that I manually enter into another spreadsheet.

Thanks for all your help!!

XLGibbs
01-10-2007, 02:07 PM
OKay CodeMakr. I will take a look later on once I get the chance....if I forget to get back to you, just shoot me a reply or a PM to follow up.

CodeMakr
01-10-2007, 02:58 PM
I've been messing with this :banghead: and think I have it working. However, not sure if most efficient, or if it will work in all situations. Can you take a look and let me know what you think :friends:

After I set my references to the 2 affected workbooks(sheets):

Dim i As Long

' Open RELB text file and set reference to it
Workbooks.OpenText Filename:="F:\Allocation - RELB.txt", Origin:=xlWindows _
, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array( _
21, 1), Array(34, 1), Array(60, 1), Array(87, 1))
Set wbRELB = Application.Workbooks("Allocation - RELB.txt").Worksheets("Allocation - RELB")

' Delete empty rows in text data file
wbRELB.Activate
For i = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Application.WorksheetFunction.CountA(Sheets(1).Cells(i, 1).EntireRow) = 0 Then
Sheets(1).Cells(i, 1).EntireRow.Delete
End If
Next
' Combine PO number and PO Line number into 1 value in Column C, add date in column D and format
For i = 1 To Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
If Sheets(1).Cells(i, 2).Value = "REL" Then
Sheets(1).Cells(i, 3).Value = Sheets(1).Cells(i, 3).Value & "-" & Sheets(1).Cells(i, 4).Value
Sheets(1).Cells(i, 4).FormulaR1C1 = "=WORKDAY(TODAY(), 4)"
Sheets(1).Cells(i, 4).NumberFormat = "m/d/yyyy"
Sheets(1).Cells(i, 6).Value = "RELB @ PKG"
Sheets(1).Cells(i, 7).Value = "300"
End If
Next
' Copy first 5 columns of data to main spreadsheet at first empty row from bottom in column "A"
Range(("A1"), Cells(Rows.Count, 1).End(xlUp).Resize(, 5)).Copy wbAlloc.Cells(Rows.Count, 1).End(xlUp).Offset(1)
' Copy 6th&7th column to main spreadsheet at first empty row in col"J"
wbRELB.Activate
Range(("F1"), Cells(Rows.Count, 6).End(xlUp).Resize(, 2)).Copy wbAlloc.Cells(Rows.Count, 10).End(xlUp).Offset(1, -1)

' Close text spreadsheet
wbRELB.Activate
ActiveWindow.Close

CodeMakr
01-10-2007, 03:18 PM
Edit: I'm sorry....I left out a huge part! My apologies :doh:

I have done a bit more work in Access to get some cleaner data. The latest code I submitted works with this new raw data set (attached). I am still having trouble combining the quantities for like part#/PO/line :dunno

So for the time being I've settled for multiple lines instead of just one.
Mark

CodeMakr
01-11-2007, 11:29 AM
I just had to update from excel 2000 to 03SR1, and now the =WORKDAY(Today(), 4) formula doesn't work?? I get a Name? error for the workday function itself. Has that changed from 00 version to 03?

XLGibbs
01-12-2007, 10:53 AM
Sorry, have had some internet and work related issue to contend with the last couple days. I will take a look for you this evening to see.

Workday might be an Analysis Toolpak formula...make sure the add in is enabled.

CodeMakr
01-19-2007, 03:53 PM
Turned out that the column format picked up a text format when it was inserted. Workday formula works fine after reformat.