I have a model that I am building that indicates whether particular SKUs (sale items) have inventory or are "stocked out". I'd like to use a formula to generate a list of only those items that are stocked out and not to paste over anything that has a positive inventory value. I know I can use filter and a pivot table to accomplish this, but want to do this with a formula and not using pivot table. Thanks!
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Copy Only Rows with Zero Inventory to Another Sheet
- Thread starter onplan
- Start date
sunny102
Board Regular
- Joined
- Dec 19, 2016
- Messages
- 102
Hi,
Use the array formula given below, and drag it down, it will return the values in column B, and assumes that your inventory is in column A and D2 is the value you want to look for in Column A and return matching values from column B, so if you are looking for 0 inventory just write 0 in d2, alternatively, if you can share format of your table here, I'll adjust the formula accordingly.
=IFERROR(INDEX($B$2:$B$60, SMALL(IF($D$2=$A$2:$A$60, ROW($A$2:$A$60)-MIN(ROW($A$2:$A$60))+1, ""), ROW(A1))),"")
Use the array formula given below, and drag it down, it will return the values in column B, and assumes that your inventory is in column A and D2 is the value you want to look for in Column A and return matching values from column B, so if you are looking for 0 inventory just write 0 in d2, alternatively, if you can share format of your table here, I'll adjust the formula accordingly.
=IFERROR(INDEX($B$2:$B$60, SMALL(IF($D$2=$A$2:$A$60, ROW($A$2:$A$60)-MIN(ROW($A$2:$A$60))+1, ""), ROW(A1))),"")
Upvote
0
Similar threads
- Question
- Question
- Question
