Almost correct.. needs one tweak

[BPS4690]

Hello... Ive spent a couple of days perusing this wonderful forum and found a great solution to my problem, but there's just one thing preventing it from functioning properly... the old "blank cell that isn't truly blank" conundrum.

Here's what Im trying to do. (from actual workbook)
I want to populate a list only of items which have a value after them. If no value, then do not display.
Easy enough for static data.. but this is not my case.



COLUMN COLUMN
AM AO
[TABLE="class: m_-4287663419734647240gmail-wysiwyg_dashes, width: 216"]
<tbody>[TR]
[TD="colspan: 2"]Hibachi Salad[/TD]
[TD]0.625[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Balsamic Vinaigrette[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BBQ[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Buffalo-Garlic[/TD]
[TD]0.125[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Sauce, cashew cream base[/TD]
[TD]0.125[/TD]
[/TR]
[TR]
[TD="colspan: 2"]Cashew Cremoso[/TD]
[TD]0.125[/TD]
[/TR]
</tbody>[/TABLE]

Where both columns AM and AO are referencing other parts of the same sheet. These are not static values, rather, they are linked to a menu elsewhere... hence when column AO does not show a value, it still contains a formula, which renders my potential solution worthless.

Im using the following array formula to only display rows where AO does not equal "".

In column AM... =IFERROR(INDEX(AI$49:AI$<wbr>81,SMALL(IF(AJ$49:AJ$81<>"",<wbr>ROW(AI$49:AI$81)-ROW(AI$49)+1)<wbr>,ROWS(AI$49:AI49))),"")

In column AO... =IFERROR(INDEX(AJ$49:AJ$<wbr>81,SMALL(IF(AJ$49:AJ$81<>"",<wbr>ROW(AI$49:AI$81)-ROW(AI$49)+1)<wbr>,ROWS(AI$49:AI49))),"")

WHen I test it against cells with static data, it works great and the blank cells cause both the item and the number to not appear at all in the list (as is the goal).. but when i try it in my recipe list, which is linked, the blank cells are not seen as ""...

How can I edit the above formula to account for this?

Thank you to anyone who can help.

 

[Jonmo1]

Welcome to the board.

Try changing
AJ$49:AJ$81<>""
to
LEN(AJ$49:AJ$81)>0

 

[BPS4690]

Thanks for the reply, Jonmo1

As such?

=IFERROR(INDEX(AI$49:AI$81,SMALL(IF(LEN(AJ$49:AJ$81)>0,ROW(AI$49:AI$81)-ROW(AI$49)+1),ROWS(AI$49:AI49))),"")

=IFERROR(INDEX(AJ$49:AJ$81,SMALL(IF(LEN(AJ$49:AJ$81)>0,ROW(AI$49:AI$81)-ROW(AI$49)+1),ROWS(AI$49:AI49))),"")

This did not cause the cells with "0" or "" to go away.. I did remember to "ctrl, shift, enter" for the array..
hmmm..

 

[Robby19]

In column AP

=IF(AO1=">0",AO1,"")

Drag all the way down to bottom. Filter Column AP to not show blanks.

 

[BPS4690]

Thanks for the reply, Robbie19.. but alas, this is not a solution.. I need the item name as well as the quantity to only appear if there is a value. The blank cells are not truly blank because they have a formula in them. (see original post)

[TABLE="width: 288"]
<colgroup><col width="72" span="4" style="width: 54pt;"></colgroup><tbody>[TR]
[TD="class: gmail-xl65, width: 144, colspan: 2"]Hibachi Salad[/TD]
[TD="class: gmail-xl66, width: 72"]0.625[/TD]
[TD="class: gmail-xl67, width: 72"][/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, colspan: 2"]Balsamic Vinaigrette[/TD]
[TD="class: gmail-xl66"][/TD]
[TD="class: gmail-xl67"][/TD]
[/TR]
[TR]
[TD="class: gmail-xl65"]BBQ[/TD]
[TD][/TD]
[TD="class: gmail-xl66"][/TD]
[TD="class: gmail-xl67"][/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, colspan: 2"]Buffalo-Garlic[/TD]
[TD="class: gmail-xl66"]0.125[/TD]
[TD="class: gmail-xl67"][/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, colspan: 2"]Sauce, cashew cream base[/TD]
[TD="class: gmail-xl66"]0.125[/TD]
[TD="class: gmail-xl67"][/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, colspan: 2"]Cashew Cremoso[/TD]
[TD="class: gmail-xl66"]0.125[/TD]
[TD="class: gmail-xl67"][/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, colspan: 2"]Caesar Dressing[/TD]
[TD="class: gmail-xl66"][/TD]
[TD="class: gmail-xl67"][/TD]
[/TR]
[TR]
[TD="class: gmail-xl65, colspan: 2"]Cranberry Vinaigrette[/TD]
[TD="class: gmail-xl66"][/TD]
[TD="class: gmail-xl67"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

@ BPS4690

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr][tr][td]

1​

[/td][td]Hibachi Salad[/td][td][/td][td]

0.625​

[/td][/tr]
[tr][td]

2​

[/td][td]Balsamic Vinaigrette[/td][td][/td][td][/td][/tr]
[tr][td]

3​

[/td][td]BBQ[/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td]Buffalo-Garlic[/td][td][/td][td]

0.125​

[/td][/tr]
[tr][td]

5​

[/td][td]Sauce, cashew cream base[/td][td][/td][td]

0.125​

[/td][/tr]
[tr][td]

6​

[/td][td]Cashew Cremoso[/td][td][/td][td]

0.125​

[/td][/tr]
[tr][td]

7​

[/td][td]Caesar Dressing[/td][td][/td][td][/td][/tr]
[tr][td]

8​

[/td][td]Cranberry Vinaigrette[/td][td][/td][td][/td][/tr]
[/table]

These appear to be your data, where cells of A:B are merged. If so, what is the expected output for this data?

 

[Robby19]

You could use a Pivot Table and filter the Values (which includes output from formulas and errors) greater than 0. This would give you your variable list. Maybe I am just not understanding all the way.

 

[Robby19]

Thanks for the reply, Robbie19.. but alas, this is not a solution.. I need the item name as well as the quantity to only appear if there is a value. The blank cells are not truly blank because they have a formula in them. (see original post)

By filtering to "not show blanks" in column AP as per my formula above, this would show the following;

Hibachi Salad 0.625
Buffalo-Garlic 0.125
Sauce, cashew cream base 0.125
Cashew Cremoso 0.125

The reported blanks from my IF statement would count as "NULL" or blank cells and thus be able to be filtered. You could also filter column AO and remove formula errors. This should give you the same response.

 

[BPS4690]

[TABLE="width: 201"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Item[/TD]
[TD] Qt[/TD]
[/TR]
[TR]
[TD]Hibachi Salad[/TD]
[TD="align: right"]0.63[/TD]
[/TR]
[TR]
[TD]Balsamic Vinaigrette[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BBQ[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Buffalo-Garlic[/TD]
[TD="align: right"]0.13[/TD]
[/TR]
[TR]
[TD]Sauce, cashew cream base[/TD]
[TD="align: right"]0.13[/TD]
[/TR]
[TR]
[TD]Cashew Cremoso[/TD]
[TD="align: right"]0.13[/TD]
[/TR]
[TR]
[TD]Caesar Dressing[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Cranberry Vinaigrette[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]


@aladin I cleaned up the sheet and removed the column in between the item and quantity as it was only there to not cut off the text in column A

So now the "item" and "qt" columns are right next to each other... here's what I need to see ...

Hibachi Salad 0.625
Buffalo-Garlic 0.125
Sauce, cashew cream base 0.125
Cashew Cremoso 0.125

Where all items that have 0 as the quantity disappear.. Robby seems to have crqcked the code but when I try it on my end it won't fire.. I'm sure do to some error Im making, but still.

 

[Aladin Akyurek]

Sheet1 (input)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][/tr][tr][td]

1​

[/td][td]Item[/td][td]Qt[/td][/tr]
[tr][td]

2​

[/td][td]Hibachi Salad[/td][td]

0.63​

[/td][/tr]
[tr][td]

3​

[/td][td]Balsamic Vinaigrette[/td][td][/td][/tr]
[tr][td]

4​

[/td][td]BBQ[/td][td][/td][/tr]
[tr][td]

5​

[/td][td]Buffalo-Garlic[/td][td]

0.13​

[/td][/tr]
[tr][td]

6​

[/td][td]Sauce, cashew cream base[/td][td]

0.13​

[/td][/tr]
[tr][td]

7​

[/td][td]Cashew Cremoso[/td][td]

0.13​

[/td][/tr]
[tr][td]

8​

[/td][td]Caesar Dressing[/td][td][/td][/tr]
[tr][td]

9​

[/td][td]Cranberry Vinaigrette[/td][td][/td][/tr]
[/table]

Sheet2 (output)

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][/tr][tr][td]

1​

[/td][td]

4​

[/td][td][/td][/tr]
[tr][td]

2​

[/td][td]item[/td][td]qty[/td][/tr]
[tr][td]

3​

[/td][td]Hibachi Salad[/td][td]

0.63​

[/td][/tr]
[tr][td]

4​

[/td][td]Buffalo-Garlic[/td][td]

0.13​

[/td][/tr]
[tr][td]

5​

[/td][td]Sauce, cashew cream base[/td][td]

0.13​

[/td][/tr]
[tr][td]

6​

[/td][td]Cashew Cremoso[/td][td]

0.13​

[/td][/tr]
[tr][td]

7​

[/td][td][/td][td][/td][/tr]
[/table]

In A1 of Sheet2 control+shift+enter, not just enter:

=SUM(IF(LEN(Sheet1!$B$2:$B$9),IF(ISNUMBER(Sheet1!B2:B9+0),1)))

In A3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$3:A3)>$A$1,"",INDEX(Sheet1!$A$2:$A$9,SMALL(IF(LEN(Sheet1!$B$2:$B$9),IF(ISNUMBER(Sheet1!$B$2:$B$9),ROW(Sheet1!$A$2:$A$9)-ROW(Sheet1!$A$2)+1)),ROWS($A$3:A3))))

In B3 just enter and copy down:

=IF($A3="","",VLOOKUP($A3,Sheet1!$A:$B,2,0))

If the source contains repeating items, run the following formula in B3 instead:

=IF($A3="","",INDEX(Sheet1!$B$2:$B$10,SMALL(IF(Sheet1!$A$2:$A$10=$A3,ROW(Sheet1!$A$2:$A$10)-ROW(Sheet1!$A$2)+1),COUNTIFS($A$3:A3,A3))))