Extract list of non-zero cell values from a list AND whether Estimate or Actual AND the name of the product

KarenAus

New Member
I have a list that I need to remove the zero value items from via formula to create a new list (imagine an order form).

Because people will be using this second form without much excel knowledge, I need it 100% formula driven.

 Qty Item Estimate or Actual? - Apples Actual 16 Bananas Estimate 15 Oranges Actual Strawberries Actual 10 Blueberries Estimate

<colgroup><col><col><col></colgroup><tbody>
</tbody>

I need it to look like this (with a dollar symbol aligned to the left):

 Bananas \$ (E) 16 Oranges \$ (A) 15 Blueberries \$ (E) 10

<tbody>
</tbody>

I have found this formula to return the non-zero values (when dragged down the column): {=IF(ROWS(F\$2:F2)>COUNTIF(A\$2:A\$1201,">0"),"",INDEX(A\$2:A\$1201,SMALL(IF(A\$2:A\$1201>0,ROW(A\$2:A\$1201)-ROW(A\$2)+1),ROWS(F\$2:F2))))}

But I'm not sure how to concatenate the estimate or \$ symbol into that formula.

Also, not sure how to extract the label in the cell beside the non-zero value.

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Eric W

MrExcel MVP
Welcome to MrExcel!

Try: