Jul 30 2020 10:23 AM - edited Jul 30 2020 11:00 AM
Jul 30 2020 10:23 AM - edited Jul 30 2020 11:00 AM
I hoping to get some fresh thoughts on a problem I'm trying to solve in excel.
I work in a manufacturing plant and my job is to track the location of certain product as they move through various control points. Let's say I have 5 control points (CP1-CP5.) This product, though it's manufacturing life cycle, will transition to and from some of these control points. Every time a person moves a piece of product from one CP to another CP they create a report listing the date, where it's moving from and where it's moving to. I keep a summary of all these reports in a table. If I want to find the current location of, for example, piece X1, I simply find the most recent entry in the table for X1.
What I'm trying to figure out is how to look at a certain CP and find out everything that's still in there. When I do that now, I have a lot of entries, but some of these pieces of product will have moved on.
Example sheet added.
Any idea would be appreciated.
Jul 30 2020 10:32 AM
Jul 30 2020 10:36 AM
Do you have the most recent version of Excel? Can you enter a formula using the FILTER function, specifically? If so, you should be able to create a formula that would sift through that table you have and use such criteria as the designation of the CP in question, as well as a "window" of date&time using the time stamps of movement....
Another possibility that occurs to me is the Pivot Table with date and time being used as filter(s) for the data to be displayed.
Is it possible for you to post a mock up of your actual file so as to enable me or somebody else here to see the kind of layout of your data? It's hard to give specific suggestions with only a verbal description; and an actual spreadsheet--just not containing proprietary info--is far more helpful than an image.
Jul 30 2020 10:42 AM - edited Jul 30 2020 10:46 AM
There are multiple way I could imagine to get what you want but having an actual sample sheet would significantly help us cater a formula for what you want. That said if you want to give it a try here are some thing I would consider:
a) filter by unique product and station then check max time stamp then filter again by only product 'coming' to that station
b) use countifs to determine which products have an ODD number (unless it is the 1st station which you would be looking for an EVEN number unless you have an line indicating the arrival of the product at station 1 also) of entries. Or if you other entries then use countifs arrival > countifs departure.
Jul 30 2020 11:07 AM
How was the column "Current Location" determined? Couldn't you simply filter this column to determine everything that is being held in a certain location?
Additionally, I do not see anything representing control points (CP). Is this represented by the "Current Location" column?
Jul 30 2020 11:22 AM
The "CP" I was referring to is the location. Each of these entries in this workbook logs a move from the "previous location" to the "current Location." What I'm looking to do is query a specific "current location" and find all the product that is in there. For example, say I have lots "abc1" through "abc50." Each of these lots may have multiple entries in the table. Going from location 1 to 5, back to 1, then to 3. What I'm looking to do is for every unique "current location" find all the lots which have the most recent "current location" entry of that location.
I'm not sure if that makes sense or not.
Jul 30 2020 11:25 AM
@aludka in addition to question by@PReagan I also don't follow this table. There isn't a unique product to track. the "Form Factor" has a general product that has parts all over the place and the program name and lot numbers are unique numbers throughout. Basically there is no specific LOT that is being tracked from station 1 to station 2 to ... Furthermore, most of the 'previous locations' don't ever have a present location so presumably they are all original source locations and most of the present locations are not represented by a previous location so are presumably final destinations. So if you have 50 units go from station SSU1 to SSU6 and 100 units go from SSU6 to customer how do you know how many units are at SSU6 since they can both originate there and get transferred there.
Jul 30 2020 12:11 PM
As @mtarler noted, a unique ID for each incoming and outgoing product is almost necessary to track what is where. With that in mind, the following workbook may not be entirely accurate. In the attached workbook, I have included a pivot table which can be filtered to find what products have come from where and what products are held in a current location. Simply change the "Current Location" and/or the "Previous Location" in the pivot table.
Jul 30 2020 12:12 PM
I wonder if you're going to get a flurry of possible answers. I'm attaching one that makes primary use of the FILTER function, only available in the newest versions of Excel.
There's a cell with yellow background where you can select the code for one of your "current locations" --when you do that, the date below will automatically populate with the latest date for which there's an entry for that location.
Then the list will automatically populate with the rows that correspond to that location and date.
Jul 30 2020 12:17 PM
as others have noted, you may need to refine your table to be more specific on actual products...or maybe we're just not seeing what's there in front of us.
Our methods (pregan's pivot table, my use of FILTER) may suffice to give you that "fresh insight" you were seeking, in any event.
Jul 31 2020 03:20 AM
The only thing I could imagine is to find the last change with VBA date and user and to see who was the last user… ergo the last “x”
Example VBA code:
For example, when the status in cell B3 changes in cell C3, the date of the change should be displayed and in cell D3 the user name that changed B3. Changes to cell B4 should be reflected in C4 and D4, etc.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Error
If Not Intersect(Range("B:B"), Target) Is Nothing Then
If Target.Row = 1 Then Exit Sub
For Each z In Target
If z.Offset(0, -1) <> "" Then
Application.EnableEvents = False
z.Offset(0, 1) = Format(Date, "DD.MM.YYYY")
z.Offset(0, 2) = Environ("Username")
' Error handling
Application.EnableEvents = True
If Err.Number <> 0 Then MsgBox "Error: " & _
Err.Number & vbLf & Err.Description: Err.Clear
If it helped, I am very happy ... please mark this as the correct answer so that others are informed. If not, please give a short feedback, maybe the other helpers could help more than me.
I know I don't know anything (Socrates)