none
seemingly simple question for Excel 365 - How can I generate an "inventory" list based on other columns? RRS feed

  • Question

  • I'm running into a brainf$rt with what seems like a simple task:

    I have an inventory list that shows some product in stock, and some product not in stock. I'm trying to generate a secondary list that only shows the items that are >0 (so in stock). Example:

    Column A       Column B                            New Column 1           New Column 2

     Apples               1                                          Apples                      1

     Bananas            0                                          Oranges                    2

     Pears                0                                          Tomatoes                  1

     Oranges            2

     Picassos            0

     Tomatoes          1

    I've been playing with index-match functions, but after two days of (what I thought was far more complicated) formulas and VBA on other worksheets, this is where I'm striking out. I'm not even sure what to google to try and find a solution.

    Can anyone offer a direction on how to extract the data?

    Thank you!

    Monday, October 14, 2019 5:38 PM

Answers

  • The question may be simple but the formula is complicated:

    In a cell in row 1, enter the following array formula, confirmed with Ctrl+Shift+Enter(this is essential):

    =IFERROR(INDEX($A$1:$B$6, SMALL(IF($B$1:$B$6>0, MATCH(ROW($A$1:$A$6), ROW($A$1:$A$6)), ""), ROWS($A$1:$A1)), COLUMNS($A$1:A$1)), "")

    Fill to the right to the next column, then fill down as far as you want.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, October 14, 2019 8:16 PM