Forum Discussion
Fresh insight to an excel problem
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.
18 Replies
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.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.
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.
- Did I get it right in the translation? Would you like to find X1 in the workbook?
If it is only that, then CTRL + F key, pop-up menu Search and Replace appears. Search for: enter the desired name and if you continue searching, it shows the first area with such an entry ... even if I don't think that's what you're looking for, I'll just send it as an additional thought.
If this is not desired, please give a short feedback.
Glad to help how I am helped.
Nikolino
I know I don't know anything (Socrates)I'm not looking to find X1. I'm looking to query a certain location and find all the product that's currently logged into that location.
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)
Dim z
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")
End If
Next
End If
' Error handling
Err.Clear
Error:
Application.EnableEvents = True
If Err.Number <> 0 Then MsgBox "Error: " & _
Err.Number & vbLf & Err.Description: Err.Clear
End Sub
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.
Nikolino
I know I don't know anything (Socrates)
You can make your spreadsheet available so that I can analyze and develop a solution for that?
