Excel cross search

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

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?

For future reference, you were NOT asked to share your file... but a SAMPLE... which can be mocked up and anonymised. The reason we ask for samples is two-fold.

1. We do not have to spend time reproducing what you already have in front of you, and

2. We can see the layout, cell references, etc, which are not always apparent from the text.

And, I suppose, also for a third reason... the EF platform is very good at messing up the formatting of anything you post in the body of the thread....