Forum Discussion
Fresh insight to an excel problem
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)
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?
aludka in addition to question byPReagan 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.
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.
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.
