Fresh insight to an excel problem

Copper Contributor

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

Hello @aludka,

 

Though I have some ideas on how to tackle this, it would be much easier to visualize with a sample of the data that you are working with. Could you please share a sample of your workbook? (please remove any sensitive data)

You can make your spreadsheet available so that I can analyze and develop a solution for that?

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)

@aludka 

 

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.

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.

@PReagan 

 

Example book posted below. Thank you.

@Aleomar 

Example book posted. Thank you.

@NikolinoDE 

 

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.

@mathetes 

 

Example book posted. Thank you.

@mtarler 

 

Example book posted. Thank you.

@aludka

 

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?

@PReagan 

 

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.

@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.

 

@aludka

 

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.

@aludka 

 

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.

@aludka 

 

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.

@aludka 

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)