•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Inventory Audit using XLookup w/ multiple criteria
Our take
Managing inventory can be complex, especially when you're trying to reconcile numbers from different sources. If you're struggling to efficiently compare your daily totals with third-party data using XLookup or nested formulas, you're not alone. This task becomes even trickier with mixed orientations—horizontal and vertical data. To streamline your process, consider focusing on a method that aligns your data structure more effectively. By leveraging the right formula approach, you can accurately populate the "Double Check" column and ensure your inventory records match seamlessly.
I've been working on a spreadsheet to keep track of some inventory. It has how many units entered and exited each day, for each item. Using this information, I have then compare it to the list of in/out inventory sent to us by a third party, basically to confirm that our numbers and their numbers match.
This is where I'm struggling. I can't seem to find an efficient way of comparing these numbers that works. I've tried XLookup and Nested formulas, Index Match, etc and nothing seems to be working.
I need the "total" numbers for each day from the first image to go into the "Double Check" column on the second image, matched for the item and date.
I'm not sure if it's an issue because some is horizontal and some is vertical?
Would love if anyone had a solution or better method to do this, thanks!
Edit for more info: the image with the inventory, the "in" and "out" columns both pull data from a separate sheet that has all of the relevant information and then the "total" column is a formula that just sums the in/out with the previous days' inventory count.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Excel seems to think cell isn't big enough so 2 digit number looks crazy, no adjustment fixes issueI have multiple sheets that add together to make a total on the first page. Easy. The numbers are small - 3, 5, 10, nothing big. On the totals sheet, all the two digit sums are wrapped so that they are very small. I do not have wrapped text on but when I DO wrap the text, it stops wrapping them BUT there's this giant space between the two digits like "1 0" for 10. I tried everything I could think of - rewriting the formula as a SUM function (rather than this sheet + this sheet + this sheet), making sure each cell is formatted as a number, running "text to column" on each column of data, switching the font, switching the font size, changing the column size manually, double clicking on the columns to have them auto size, turning off shrink to fit, turning on shrink to fit again after, playing with the alignment.... Does anyone know what is going on and how I can solve it? Adding images (w/ some edits for privacy for my company). Will include the formulas (both versions). https://preview.redd.it/6n05pqeer4og1.png?width=1654&format=png&auto=webp&s=64729e1558bfba595d7d043b18d777b3e33e4ecf https://preview.redd.it/ydydstbfr4og1.png?width=1664&format=png&auto=webp&s=27e3d7cd18c2c380a5cf1a48853060a4bcd17c7c https://preview.redd.it/7dcraf9gr4og1.png?width=1106&format=png&auto=webp&s=4c93c635fbc20762a18cc8e137b6a40ee37c470e submitted by /u/catincombatboots [link] [comments]
- Simple Inventory In-Out Sheethttps://preview.redd.it/khli3c681ang1.png?width=1235&format=png&auto=webp&s=9fc6254e73d91e8684d1c5d7de323b78a2698b4a Where have I gone wrong here? How can I make the balance of the eggs from this example read "1"? I was using a different function previously - ex.: =SUMIF(B6:B200,H6,C6:C200)-SUMIF(E6:E200,H6,F6:F200) The problem I had was that the range was static and would move as I added rows so I would have to manually move the top of the range up to the first row and move the bottom of the range down. I don't use excel much I just need this simple balance sheet for keeping track of inventory, ideally minimizing the amout of manual labour I have to put into it (lol) any help would be greatly appreciated. submitted by /u/sikdoo223 [link] [comments]
- Complicated Lookup Function - Pulling Info From One Sheet To AnotherMaybe this isn't as complicated as it seems to be in my head, but essentially I'm trying to pull inventory numbers from one spreadsheet into another. However, the numbers I need to pull in Spreadsheet 1 are listed vertically and I'd like that info to be listed horizontally on Spreadsheet 2. Here is a quick reference of what I'm trying to achieve. I was able to pull the product names to my second sheet using a VLookup function (woo-hoo!) but trying to pull the inventory numbers by location to Sheet 2 is proving... difficult. Very difficult for me, anyway, especially given how large the spreadsheet is and how the info is formatted. Additionally, some products have additional locations beyond what is listed below - for example a few randomly have a location called "EVENTS", so the Shop Names aren't necessarily consistently placed within the table. So it really needs to be a function that looks up the Qty next to the Shop Name listed under the Item #? Is that even possible? I've color coded what info I would like to pull and where I need it placed (lighter shades = Info I need pulled, darker shades = shop locations I'm referencing). I hope that makes sense! SHEET 1 SHEET 2 submitted by /u/SameCoffeeEveryDay [link] [comments]
- Issue with spreadsheet containing time calculation formulasI have a new clock in/out machine which has 3 columns, 1 for clock in, 2 for clock out and a 3rd column for the total time worked. Staff can clock in/out multiple times a day. It comes to me as an xls file with all the in/out cells as 'general' format and the 3rd column is as custom hh:mm but not the subtraction formula to show the total time worked even though the numbers are right. If i change the in/out from general to custom and then hh:mm I seem to have to click each individual cell for the format to change from (ie) 09:58 AM to 09:58 without the AM but the PM cells still show PM and when i do the subtraction formula for the total time worked it comes out as an error value, no amount of trying different formulas will change it to what it needs to be. I don't mind the first 2 colums showing as AM/PM but even with them that way the subtraction for the total time calculation still comes up as with a 'value' error https://preview.redd.it/hvunwr4p58og1.png?width=512&format=png&auto=webp&s=c11e3a1b77c137a4a40544c7bb2274e813d1038c submitted by /u/Loud-Conference8055 [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#natural language processing for spreadsheets#financial modeling with spreadsheets#rows.com#AI formula generation techniques#big data management in spreadsheets#modern spreadsheet innovations#machine learning in spreadsheet applications#enterprise-level spreadsheet solutions#digital transformation in spreadsheet software#conversational data analysis#Excel compatibility#collaborative spreadsheet tools#AI-driven spreadsheet solutions#cloud-based spreadsheet applications#real-time data collaboration#automation in spreadsheet workflows#intelligent data visualization#no-code spreadsheet solutions