Return list with non-zero and non-blank values from a range with multiple columns

[olavimuff]

Hello

I am new here and I already found a thread with similar issue, but I want it a bit different solution.

My purpose is to make a list of items and each item have amount of quantity required to buy. By default their value is 0 until I increase it. And there is another section of data which makes summary of long list by displaying only these items which required amount>0.

I have uploaded a file which displays which result I want. I found a formula, which reveals only values of quantities, but I also want right item name on front of this value.

Olavi

[AliGW]

Welcome to the forum.

In F4:

=IFERROR(INDEX($A$1:$A$10,SMALL(IF($B$3:$B$10>0,ROW($B$3:$B$9),""),ROW(B1))),"")

In G4:

=IFERROR(INDEX($B$1:$B$10,SMALL(IF($B$3:$B$10>0,ROW($B$3:$B$9),""),ROW(B1))),"")

In H4:

=IFERROR(INDEX($C$1:$C$10,SMALL(IF($B$3:$B$10>0,ROW($B$3:$B$9),""),ROW(B1))),"")

[JohnTopley]

In F4

=IFERROR(INDEX($A$4:$A$9,AGGREGATE(15,6,(ROW($A$4:$A$9)-ROW($A$4)+1)/($B$4:$B$9>0),ROWS($1:1))),"")

in G4

=IF($F4="","",VLOOKUP($F4,$A$4:$C$9,2,FALSE))

in H4

=IF($G4="","",VLOOKUP($F4,$A$4:$C$9,3,FALSE))

[olavimuff]

Wow, both solutions works. I was so close but still too far from Ali's solution. I am not familiar with Excel code at all, but now it looks logic.

Thank you very much, Ali and John! Have a nice day! I can use that small code now to organize my work a bit.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[wk9128]

Cell F4 formula , Drag down and across

If you have 365, one formula solves all

Cell F4 formula

[olavimuff]

wk9128 - nice, only 1 formula needed. Works, thanks!

I use office 2016.

[AliGW]

If you have FILTER, then you do NOT have Excel 2016. It's only available in MS365, so please update your profile accordingly.

[olavimuff]

Hello

I have one more question. Since there are like 800 items in long list and I can filter these by their quantity, can I now remove items from short list by editing or resetting quantity of correspondent item in the long list?

Example: I needed 22 pcs of Item6. I bought that amount and now I want to set its amount back to 0. It can be much easier to find Item6 from filtered list instead of searching item from long list.

Can I do this somehow? Excel 2016.

[JohnTopley]

If you want to re-rest column B back to zero you will need VBA to do this: plus I suspect your sample is an over-simplification of your real situation.

[olavimuff]

John, I was afraid that its not so easy anymore. VBA seems easy, but I prefer to use excel online and it seems it does not have support of VBA.

I can do my work with that way too. I dont need to use it every day and can find items by their unique product code.