How to list all values in column B that have a lower value in column E than the connected value in column C
Our take
This proved to be extremely difficult to explain in the title, so apologies for the cryptic header.
I'm essentially trying to find a formula that gives me the same as in the "Goal" array from the "Inventory Count" array. My plan is to find a formula that gives me the values from the B (Item) column, only if the matching value in the E (current) column, is less than the matching value in the C (min) column. I will then use Xlookup to find the inventory value, and xlookup-inventory to figure out how much to produce to end up at the maximum value (See column M (needed).)
I've tried using a sort(filter()) formula, but this only returns "failed" (failed is my own word I make it say when it isn't able to complete the request), it also only returns it in one cell. The final part for the sort part of the formula is to be able to have it sorted from smallest to largest in the "needed" column, but this might need a few workarounds, so my main focus is just to have it return the neccesarry Item values in any order.
Below is another attempt of mine, here I was able to have it return a full list, but it still only returns "failed"
This is a slightly simplified sheet from the real thing. In the actual sheet I have other things below the "inventory Count" array, in the same columns, so using the entire column as a ref will not be possible. The values is column E (current) are also the result of a formula, and is subject to change.
Thanks for any help!
EDIT:
Thanks so much for all the response! Especially everyone who tested my formula for themselves and showed me that it worked!
It turns out that I wrongly assumed the problem was somewhere in the formula itsself. In fact the problem turned out to be that Excel didn't read the numbers in the "Current" column as numbers, even after I changed them from general to numbers. I ended up figuring this out by attempting to add a decimal to all the numbers in the sheet, and the ones in the "current" column didn't change.
The problem was in the formula I used to get the numbers for the "current" column from a different array.
=IFNA(TEXTAFTER(INDEX(BO:BO,MATCH(BQ5,BO:BO,0)+1,1),"x "),"")
The solution was as simple as just adding a =NUMBERVALUE() around the entire formula. In the end this is the final product!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Anomalous Sorting of COUNTIF ColumnsEDIT: Resolved as I was trimming the worksheet to upload a version with data redacted with replacement text. As I was removing extraneous worksheets, the formula broke with a #REF value. When I fixed them, the problem resolved. Looks like I was actually connected to another set of the same data, but since it as outside of the table, it was creating the anomaly inside of it. Essentially the issue outlined in this blog article, except A) I am not using the unnecessary sheet reference that fixes the problem if it's removed and B) the formula displays correctly: https://excelcharts.com/excel-sort-countif-function-mess-fix-it-how-to/ (Unfortunately, the data I'm working on is proprietary, so I can't share specifics. :( Images are mockups that simulate the results I'm getting.) I have a large table on Sheet1. Column A is a Date column that runs back several years. I would like to count instances in text column B going one year back as of the most recent update and then sort that from largest to smallest, so I can make a simple line graph with the top ten items. Updated iterations of this graph will be used in a routine report, I'm ultimately trying to draft a plug and play tool so my Excel-deficient coworkers can just Copy/Paste into Powerpoint. https://preview.redd.it/ad56jbjp0oog1.png?width=364&format=png&auto=webp&s=8fc6b43dfe338b2c93d3ce7945d5fe9b7e45e0e5 On Sheet2 I create a secondary table that will have 2 columns. The text column A and the Count column B. I have entered each unique Data value from Sheet1 into the Data column in Sheet2. In the Count column I enter the following formula: =COUNTIFS('Sheet1'!A:A,">="&MAX('Sheet1'!A:A)-365,'Sheet1'!A:A,">="&MAX('Sheet1'!A:A),'Sheet1'!B:B,A#) (Where # equal the row) The table on Sheet1 has over 20 columns and I ultimately want to create line graphs for the data in several columns. Since there will be several of these graphs, and the data in Sheet1 is appended with new, up to date data at least once a week, it makes more sense to list the range as 'Sheet1'!A:A rather than 'Sheet1'!A1:A# The formula works just fine. https://preview.redd.it/0gpltacq2oog1.png?width=260&format=png&auto=webp&s=0ba8b74005b82fa9f37813dde8f838af29d49b96 But when I try to sort by Count, it does what's pretty much spelled out in the above linked article, moving the Data cell to the correct row, with the formula adjusting to reflect it's new replacement, but returning the value associated with the Data in the row it was in before the sort. So we see "Item27" in row 28 has the highest count, with 56 occurrences. The formula in row 28 column B, as expected, is: =COUNTIFS('Sheet1'!A:A,">="&MAX('Sheet1'!A:A)-365,'Sheet1'!A:A,">="&MAX('Sheet1'!A:A),'Sheet1'!B:B,A28) After the sort, "Item27" is in row 2 (as expected) but is returning a count of 4, the amount of occurrences "Item17" (now sitting in "Item27"'s old position in row 28) returned before the sort. Of course, "Item17" is returning the wrong count as well. "Item1" was obviously displaced by "Item27" and is in it's expected row 19 but returning a count of 56, which is "Item47"'s actual count. Essentially, each "Item#" is returning a count of whatever "Item#" replaced them in their old position in the table. https://preview.redd.it/aknjet2x5oog1.png?width=249&format=png&auto=webp&s=d069620eed3867df4eabae42532654c089f1cef7 HOWEVER, unlike the example in the article, the formula sitting in B2 next to "Item47" is still correct: =COUNTIFS('Sheet1'!A:A,">="&MAX('Sheet1'!A:A)-365,'Sheet1'!A:A,">="&MAX('Sheet1'!A:A),'Sheet1'!B:B,A2) So.... what's the issue and how do I resolve it? Version: Microsoft Excel for Microsoft 365 MSO (v2508) Environment: Desktop My Knowledge: Intermediate submitted by /u/LikeCCClockwork [link] [comments]
- Inventory Audit using XLookup w/ multiple criteriaI'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. https://preview.redd.it/0r74aaf1nang1.png?width=1225&format=png&auto=webp&s=b024c5dad6c53315bfc91199aed1585e74512cba https://preview.redd.it/z799p9f1nang1.png?width=535&format=png&auto=webp&s=2df6c58a1f26c43d0d0b37801eb5d03cc05bdbfd submitted by /u/Fluffy-Reaction6723 [link] [comments]
- =FILTER for scheduling, spill workaround?Hey everyone, I feel as though I am doomed here in what I'm trying to achieve here but figured I'd give it a shot. https://preview.redd.it/p7n14tnv9fog1.png?width=1072&format=png&auto=webp&s=2c524c83208bf506be664fb4beec2568dac7377c Above is the end goal where 'customer'/ 'product'/ 'order qty'/ 'ship date' is being populated based on just inputting a sales order number in column A. Each cell in the 'Customer' column is using =FILTER to spill out data into the subsequent columns by finding the sales order number from a separate master sales order sheet and pulling it from that specific column in the 'Sales Order's row. https://preview.redd.it/go7fc5p7bfog1.png?width=927&format=png&auto=webp&s=f3b30ea10e91aa4c1cf87f9ec400f4976f9215b5 Of course, above is the cruel #spill reality I am burdened with once I drag the formula cell down so this can be used dynamically. I've used XLOOKUP in the past which worked okay, but if a sales order number had multiple rows of items like "Bike"s order in the end goal picture, it only returns the first value. =FILTER(SO!$H$2:$L$69,SO!$G$2:$G$69=B6,"") This is the formula I'm using currently, maybe I'm missing something or there's some complicated work around. This sheet is to be used by multiple people of varying technical ability so being very simple is imperative. Anything is helpful or just let me know if this aint gonna work boys. submitted by /u/gotdj0nt [link] [comments]