My employer asked me to automate the duplicate positioned items in excel. Our company works with 20.000+ items and the stock in all of the locations is automatically updated each day. When new items are moved by a branch, they often forget to remove the replaced item out of our database. The location X, X, X, X (rack, shelve, spot, little compartment) now has 2 Items in the database but only 1 in reality.
If for example item D replaces item B at location 1, 1, 1, 1 and they add D in the database but forget to remove B, we see the following
With only 4 items, it is easy to see that B and D share the same position in the same branch which is an obvious mistake. With over 20.000 items, you have to manually look at each location at each branch which normally takes about 4 to 5 days.
The same X.X.X.X. values exist in different branches. the same products are in in different branches. There are around 80 branches. Each branch has up to 9 * 9 * 9 * 9 = 6.561 items (where as 9 is the highest number for a location). Here a more detailed and correct example of our database:
As you can see, the location 1, 1, 1, 1 is in every branch which is okay, but since it is twice in branch 3275, this is a problem.
Is there a way to make a calculation/formula in excel so that I only have to upload the generated XML database and the calculation gives me the duplicate items and their locations automatically?
If for example item D replaces item B at location 1, 1, 1, 1 and they add D in the database but forget to remove B, we see the following
| Item | Branch | Location nr 1 | Location nr 2 | Location nr 3 | Location nr 4 |
|---|---|---|---|---|---|
| A | 3275 | 1 | 5 | 3 | 2 |
| B | 3275 | 1 | 1 | 1 | 1 |
| C | 3275 | 1 | 3 | 1 | 2 |
| D | 3275 | 1 | 1 | 1 | 1 |
With only 4 items, it is easy to see that B and D share the same position in the same branch which is an obvious mistake. With over 20.000 items, you have to manually look at each location at each branch which normally takes about 4 to 5 days.
The same X.X.X.X. values exist in different branches. the same products are in in different branches. There are around 80 branches. Each branch has up to 9 * 9 * 9 * 9 = 6.561 items (where as 9 is the highest number for a location). Here a more detailed and correct example of our database:
| Branch | Item | Location 1 | Location 2 | Location 3 | Location 4 |
|---|---|---|---|---|---|
| 3000 | 3387 | 1 | 1 | 1 | 1 |
| 3000 | 3390 | 1 | 1 | 1 | 2 |
| 3000 | 3388 | 1 | 1 | 1 | 3 |
| 3000 | 3392 | 1 | 1 | 1 | 4 |
| 3000 | 3345 | 1 | 1 | 1 | 5 |
| 3230 | 3378 | 1 | 1 | 1 | 1 |
| 3230 | 3336 | 1 | 1 | 1 | 2 |
| 3230 | 3369 | 1 | 1 | 1 | 3 |
| 3275 | 3387 | 1 | 1 | 1 | 1 |
| 3275 | 3336 | 1 | 1 | 1 | 2 |
| 3275 | 3350 | 1 | 1 | 1 | 3 |
| 3275 | 3359 | 1 | 1 | 1 | 1 |
Is there a way to make a calculation/formula in excel so that I only have to upload the generated XML database and the calculation gives me the duplicate items and their locations automatically?
