Forum Discussion
Cross search matching data - SOLVED
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
Item, Branch, Location 1, Location 2, Location 3, Location 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 |
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?
Assuming that the layout is as in your second example: Branch in column A, Item in column B, and Locations 1 to 9 in columns C to K:
Enter a heading such as Duplicate in L1.
Enter the following formula in L2:
=COUNTIFS(A:A,A2,C:C,C2,D:D,D2,E:E,E2,F:F,F2,G:G,G2,H:H,H2,I:I,I2,J:J,J2,K:K,K2)>1Fill down to the end of the data.
You can now turn on AutoFilter, and filter column L for TRUE.
2 Replies
Assuming that the layout is as in your second example: Branch in column A, Item in column B, and Locations 1 to 9 in columns C to K:
Enter a heading such as Duplicate in L1.
Enter the following formula in L2:
=COUNTIFS(A:A,A2,C:C,C2,D:D,D2,E:E,E2,F:F,F2,G:G,G2,H:H,H2,I:I,I2,J:J,J2,K:K,K2)>1Fill down to the end of the data.
You can now turn on AutoFilter, and filter column L for TRUE.
HansVogelaar Thank you, this solves it!
