I have two columns, first is an export from stock that looks like:
ID ProductName EAN Stock
Tricks:
EAN can be repeated several times linked to different ID (basically you can buy the same product from a different supplier that would get different ID, but have same EAN)
Stock can be negative number (you messed up when doing invoice, rather easy since EAN code can repeat itself)
Then you do inventory by simply scanning all EAN codes and you get a list.
I managed to merge EAN codes with counting so I got EAN Stock
I merged (Full Outer) and it seemed to work until I noticed that when EAN codes repeat, I get repeated matches.
Perfect solution would be this:
show me a new column where I can see what items are missing and what items should not be there while also keeping an eye on negative values
Example. ProductName doesn't matter, ID doesn't matter.
ID ProductName EAN Stock
1 NameX 11 3
2 NameY 22 -2
3 NameQ 22 3
4 Name Z 11 4
Inventory (just EAN codes)
11
11
11
22
11
Result
ID ProductName EAN Stock Result
1 NameX 11 3 0 (all good, passing 1 extra to the next)
2 NameY 22 -2 0 (all good, -2+3=1-1=0)
3 NameQ 22 3 0 (all good, -2+3=1-1=0)
4 Name Z 11 4 -3 (we are missing 3 items)
