Make a new list only with categories that have a value greater than 0

[cacos]

Hi!

I have a list with different categories, and each has a value.I wan't to create a new list that feeds from this one, but only with the categories that have values >0, excluding the other.


It looks like this:


COLUMN1 COLUMN2


Category#1 4

Category#2 7

Category#3 9

Category#4 0

Category#5 26


In my new list, i'd need to display all categories except for Category#4.


Thanks!

 

[shrivallabha]

Hi Cacos,


Welcome To Chandoo!


What kind of solution are you looking for, Macro or Formula based?

 

[cacos]

Hi shrivallabha! Thanks for your quick response, if it's possible a formula would be a lot better.


PS: Love this site, it has helped me a lot in the past few months!

 

[cacos]

To be more precise, the idea is to have the new list sorted, with no blank spaces between each category. I need it to be dynamic (i.e. avoid data filter and such) since it will be feeding from other formulas.


Thank youuuuuuuuuuuuuuuuu to anyone out there with more expertise

 

[shrivallabha]

Hi Cacos,


Sorry, I logged out y'day as it was close to midnight.


Here's one formula approach. It is ARRAY formula so you need to confirm it using CTRL + SHIFT + ENTER keys simultaneously and not just plain ENTER. If you do it correctly then Excel will surround formula with curly braces {}.


Your list is in Column A & B (Row 1 is header row and data starts from Row 2)


=INDEX($A$1:$A$5000,SMALL(IF($B$2:$B$5000<>"",IF($B$2:$B$5000<>0,ROW($B$2:$B$5000))),ROWS($B$2:$B2)))

 

[cacos]

Thank you shrivallabha, I'll try it now and let you know.

 

[Luke M]

Explanation of how that formula works, if you're interested.

http://chandoo.org/wp/2011/11/18/formula-forensics-003/

You can use the technique to build a list meeting any set of criteria(s).

 

[cacos]

It worked perfectly! This is great i'm going to be using arrays a lot.


Thank you Luke M, it shed a lot of light and helped me understand it all, priceless info.