CODEWITHSUNDEEP
excel
Mike
Mike

Reputation: 567

Excel - Create list from a range where corresponding value in 0 or Null

In Excel 2013 how do I bring back a list of items with 0 sales?

eg

enter image description here

Upvotes: 1

Views: 165

Answers (2)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

Select E1:E4 range. With selected range enter following formula in formula bar:

=IFERROR(INDEX(A1:A4,SMALL(IF(B1:B4=0,ROW(A1:A4)-ROW($A$1)+1,""),ROW(INDIRECT("1:" & ROWS(A1:A4))))),"")

and since it's an array formula, press CTRL+SHIFT+ENTER to evaluate it.

enter image description here

Upvotes: 0

user2140261
user2140261

Reputation: 7993

You can set up a Pivot table like this:

enter image description here

If you really do need a formula this one will offer the best performance, as it is NOT an array formula and will perform faster and faster on longer list as you get further and further down. Because it only looks at the portion of the list below the last value instead of testing every value in the list for every word everytime.

To start enter 

=INDEX(A1:A10,MATCH(0,B1:B10,0))

In D1 to get the first value with a 0. Then in D2 enter:

=IFERROR(INDEX(INDIRECT("A"&MATCH(D1,$A$1:$A$10,0)+1&":A11"),
 MATCH(0,INDIRECT("B"&MATCH(D1,$A$1:$A$10,0)+1&":B11"),0)),"")

replacing $A$1:$A$10 with your list of items. And changing the A11 and B11 to the row below your last item.

This will result in the following:

enter image description here

Upvotes: 1

Related Questions