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

[KarenAus]

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.


[TABLE="class: grid, width: 252"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Qty[/TD]
[TD]Item[/TD]
[TD]Estimate or Actual?[/TD]
[/TR]
[TR]
[TD] - [/TD]
[TD]Apples[/TD]
[TD]Actual[/TD]
[/TR]
[TR]
[TD] 16[/TD]
[TD]Bananas[/TD]
[TD]Estimate[/TD]
[/TR]
[TR]
[TD] 15[/TD]
[TD]Oranges[/TD]
[TD]Actual[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Strawberries[/TD]
[TD]Actual[/TD]
[/TR]
[TR]
[TD] 10[/TD]
[TD]Blueberries[/TD]
[TD]Estimate[/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Bananas[/TD]
[TD]$ (E) 16[/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD]$ (A) 15[/TD]
[/TR]
[TR]
[TD]Blueberries[/TD]
[TD]$ (E) 10[/TD]
[/TR]
</tbody>[/TABLE]


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.

Any ideas please?

 

[Eric W]

Welcome to MrExcel!

Try:

Book1
	A	B	C	D	E	F
1	Qty	Item	Estimate or Actual?		Item	Result
2		Apples	Actual		Bananas	$ (E) 16
3	16	Bananas	Estimate		Oranges	$ (A) 15
4	15	Oranges	Actual		Blueberries	$ (E) 10
5		Strawberries	Actual
6	10	Blueberries	Estimate
Sheet9

Cell Formulas
Range	Formula
E2	=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($B$2:$B$6)/($A$2:$A$6>0),ROWS(E$2:E2))),"")
F2	=IF(E2="","","$ ("&LEFT(INDEX($C$2:$C$6,MATCH(E2,$B$2:$B$6,0)))&") "&INDEX($A$2:$A$6,MATCH(E2,$B$2:$B$6,0)))