Display values in separate worksheet only if that value is greater than 0.

[sparky1231]

I am trying to build a report based on data in a separate worksheet. The report must only show the name of the value and qty associated with it only if the qty is greater than 0.

As an example, the values can be colors.

Column A = Names of Colors: Red, Blue, Green, Yellow, Black
Column B = Qty of each Color: 5,4,0,1,0

The report is held on a separate spreadsheet. I would like to only show the values that have qty's greater than 0, not any values that have a value of 0.

I've attached a sample workbook just in case.

Thanks for any help!

[FDibbins]

Hi and welcome to the forum

Try tis, using a helper in "data" ( I used C, you can use whatever you want, you can also hide it). Copy this to C2, and then copy down...
=IF(B2=0,C1,C1+1)
Then in "report", use this in A5, copied down and across...
=IFERROR(INDEX(data!$A$1:$B$6,MATCH(ROW($A1),data!$C$1:$C$6,0),MATCH(report!A$4,data!$A$1:$B$1,0)),"")

[sparky1231]

That works, thanks FDibbins!!!

[Tony Valko]

Let's make this an all PA thread!

Here's another one.

On the Report sheet...

Enter this array formula** in A5:

=IFERROR(INDEX(data!A:A,SMALL(IF(data!B$2:B$6>0,ROW(data!B$2:B$6)),ROWS(A$5:A5))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Enter this formula in B5:

=IF(A5="","",VLOOKUP(A5,data!A:B,2,0))

Select A5:B5 and copy down until you get blanks.

[sparky1231]

I have been using this formula with no issues until now...I now have to account for a different if variable.

I've attached an updated workbook.

Based on the attached, I have two sets of Data to deal with. "Name" and "Description"

The report needs to show both of these fields "Name" and "Description" if the value in the description field is not = to 0. This I was able to do through the formula provided by FDibbins.

However, there are instances when the "Name" field is a duplicate (as well as corresponding "description") and I would only like said duplicated name to appear once on the report, regardless of how many times it shows up on the data tab.

For example, Name "4" shows up on the data three times. If all three instances are not=0 (or if 1 out of 3 or 2 out of 3), then the report should only show the Name and corresponding description ONCE. The only time the report would not show the Name and Description is if all three instances had 0 in the description cells.

Sorry if this is confusing, I think I am just missing one IF argument somewhere...

Thanks!

[FDibbins]

If I understand, you, try this in the helper ....
=IF(OR(B2=0,COUNTIF($A$2:A2,A2)>1),D1,D1+1)
Then ...
=IFERROR(INDEX(data!$A$1:$B$9,MATCH(ROW($A1),data!$D$1:$D$9,0),MATCH(report!A$4,data!$A$1:$B$1,0)),"")

[Tony Valko]

I the sample file Name 4 appears 3 times and all the number values are >0. However, Name 4 has 2 different Descriptions.

So, if you only want 1 instance of Name 4 in the results, which description should be included in the results?

Data Range

	A	B	C
1	Name	Description
2	1	A	1
3	2	0	1
4	3	C	2
5	4	D	3
6	4	D	4
7	4	0	4
8	5	E	5
9	6	0	5

[sparky1231]

If I understand, you, try this in the helper ....
=IF(OR(B2=0,COUNTIF($A$2:A2,A2)>1),D1,D1+1)
Then ...
=IFERROR(INDEX(data!$A$1:$B$9,MATCH(ROW($A1),data!$D$1:$D$9,0),MATCH(report!A$4,data!$A$1:$B$1,0)),"")

That works, thanks again!