ok i have an inventory question

Occasional Contributor

i work with office excel in microsoft 365 im not sure if thats helpful and i literally know absolutely nothing about the program. in fact i just learned how to type. anyhow i work in logistics and part of my job is a kind of yard goat, really. i have o keep track of the containers we have in the yard, i have to know which ones left yesterday, which ones came yesterday, and so forth. so i think that excell could help with kind of listing, i think its a particular kind of math formula that i need to tell the program to do but im just not sure how to do that. so im looking for a formla to tell the program to subtract tuesdays containers from mondays containers and return the difference in the form of a list of containers that we dont have on tuesday that were here on monday, for the containers that were returned. then i want to be able to subtract mondays containers from tuesdays containers to find out which containers were added.  can anyone  help me with this? a very helpful and much appreciated gentleman helped me out with a formula but i think it was a different version of excel that he had because when tried to impliment the formula it just didnt work

5 Replies

@NALINLE 

 

Can you type in the formula which that very helpful gentleman gave you, as a starter here. Otherwise you're asking us to do a LOT of mind-reading in even beginning to offer more help. And most of us are not licensed mind-readers. 

 

How are you (or your predecessors) doing that inventory task in the absence of a working spreadsheet? Presumably you're doing something on paper....what does that look like?

 

Anything you can provide that gives us a more complete handle on the current situation, how detailed your records are, etc.......would help. It may not be possible to attach whatever you have as an excel file, but you are allowed to embed  images in your post. Some images/screen grabs of your file, for example, or an image of a paper inventory tracker.....

@mathetes @NikolinoDE @mtarler 

   =FILTER($A$2:$A$15,COUNTIF($B$2:$B$15,$A$2:$A$15)=0) 

IS THE FORMULA I GOT From that very helpful gentleman and he understood what i wanted to do but the formula didnt work i got an error msg.  ive attached a copy of the spreadsheet ive created its just list for everyday that i inventory the containers. those lists are every other column with blank columns in between where i anticipate the formula and the results of the formula to go once i figure out what that is and how to use it. i imagine that formula there that that helpful gentlemen gave me is probably the correct formula but maybe i have a different versin of excel? i think were using microsoft 365 and its all web based. i hope that helps all you wonderful people trying to help me i really do appreciate it so so much!

so you see what i need to do is when i put the currrent days list up, i want the computer to find and list all the differences between todays and yesterdays list. so i want to see a list of all the items on yesterdays list that are not on todays list and a list of todays items that are not on yesterdays list.
?
but i dont want to alter either list, because we have to regularly refer to them

@NALINLE so I modified the previous formula because I noticed that your inventory lists are not unique ids and hence you probably want to know how MANY of each were add/removed.  in the attached I created 2 sample columns (from your data) and 2 columns using these formulas:

Changed item list (cell D2): 

=UNIQUE(FILTER(A2:A201,COUNTIF(B2:B201,A2:A201)<>COUNTIF(A2:A201,A2:A201),"none"))

Quantity of change (positive means added and negative is removed) (cell E2):

=COUNTIF(A1:A200,D2#)-COUNTIF(B1:B200,D2#)

In the above formulas I assume <200 rows to keep Excel from calculating a lot of nothing.  It would be better if the lists were in tables but this is fine if you know a max length for your lists.  If not a dynamic length can be defined but you should avoid using A:A because it may really slow excel down.

As for why the formula didn't work for you, I'm not sure and without seeing the sheet it may be hard to see if it was a typo or something, but based on the #Value error I believe that means it isn't a function compatibility issue (but I could be wrong).