Remove blank cells from if function

[Trueblue862]

I have this sheet below which pulls data from another sheet to produce a profit/loss report. Is there any way to adjust the formulas to condense the report down and remove the blank cells?

Stock List 1.6.xlsm
	A	B	C	D
1	Profit Items	Profit per Item	Loss Items	Loss Per Item
2	Apples (pink lady)	$0.00
3			Avocado	-$100.00
4	bananas (cavendish)	$0.00
5			bananas (sugar)	-$75.00
6	beans	$0.00
7			beetroot	-$32.00
Profit or Loss

Cell Formulas
Range	Formula
A2:A7	A2	=IF(INDEX('Order Sheet'!$N:$N,ROW())>=0,INDEX('Order Sheet'!$A:$A,ROW()),"")
B2:B7	B2	=IF(INDEX('Order Sheet'!$N:$N,ROW())>=0,INDEX('Order Sheet'!$N:$N,ROW()),"")
C2:C7	C2	=IF(INDEX('Order Sheet'!$N:$N,ROW())<0,INDEX('Order Sheet'!$A:$A,ROW()),"")
D2:D7	D2	=IF(INDEX('Order Sheet'!$N:$N,ROW())<0,INDEX('Order Sheet'!$N:$N,ROW()),"")

 

[CA_Punit]

So once you have the Table, it is possible to to summarize it in this way

Book1
	A	B	C	D	E	F	G	H	I
1	Profit Items	Profit per Item	Loss Items	Loss Per Item
2	Apples	200				Apples	200	Avocado	100
3			Avocado	100		Banana	500	Bananas	75
4	Banana	500				Beans	600	Beetroot	200
5			Bananas	75
6	Beans	600
7			Beetroot	200
8
9
10
11
12
13
Sheet2

Cell Formulas
Range	Formula
F2:I4	F2	=INDEX($A$1:$D$7,INT(AGGREGATE(15,6,IF($A$2:$D$7<>"",ROW($2:$7)*100+COLUMN($A:$D)),SEQUENCE(3,4))/100),MOD(AGGREGATE(15,6,IF($A$2:$D$7<>"",ROW($2:$7)*100+COLUMN($A:$D)),SEQUENCE(3,4)),100))
Dynamic array formulas.

But let me try to modify the formula

 

[Trueblue862]

Thank you, I'll give it a try.

 

[jasonb75]

Try these, I've set the formulas for data in the order sheet down to row 200, adjust as needed but realistically. Do not use entire columns anywhere that I have not already done so.

A2 =IF(A1="","",IFERROR(INDEX('Order Sheet'!$A:$A,AGGREGATE(15,6,ROW('Order Sheet'!$N$2:$N$200)/('Order Sheet'!$N$2:$N$200>0),ROWS(A$2:A2))),""))
B2 =IF(A2="","",SUMIFS('Order Sheet'!$N:$N,'Order Sheet'!$A:$A,A2))
C2 =IF(C1="","",IFERROR(INDEX('Order Sheet'!$A:$A,AGGREGATE(15,6,ROW('Order Sheet'!$N$2:$N$200)/('Order Sheet'!$N$2:$N$200>0),ROWS(C$2:C2))),""))
D2 =IF(C2="","",SUMIFS('Order Sheet'!$N:$N,'Order Sheet'!$A:$A,C2))

 

[Trueblue862]

Thank you, I’ll try them out tomorrow and let you know how I go.

 

[jasonb75]

Something that I missed earlier, is it possible that an item could appear in both the Profit and Loss columns (as with bananas in your example)?
If it is possible then the formulas for columns B and D will need modifying slightly.

 

[Trueblue862]

No, it should be either profit or loss, the two bananas in my example are two different products. Thanks for your help, I’ll give it a try tomorrow.

 

[Trueblue862]

Try these, I've set the formulas for data in the order sheet down to row 200, adjust as needed but realistically. Do not use entire columns anywhere that I have not already done so.

A2 =IF(A1="","",IFERROR(INDEX('Order Sheet'!$A:$A,AGGREGATE(15,6,ROW('Order Sheet'!$N$2:$N$200)/('Order Sheet'!$N$2:$N$200>0),ROWS(A$2:A2))),""))
B2 =IF(A2="","",SUMIFS('Order Sheet'!$N:$N,'Order Sheet'!$A:$A,A2))
C2 =IF(C1="","",IFERROR(INDEX('Order Sheet'!$A:$A,AGGREGATE(15,6,ROW('Order Sheet'!$N$2:$N$200)/('Order Sheet'!$N$2:$N$200>0),ROWS(C$2:C2))),""))
D2 =IF(C2="","",SUMIFS('Order Sheet'!$N:$N,'Order Sheet'!$A:$A,C2))

Thank you for the time you've taken with this, it's not working for me. I'm not worried about messing with it anymore, it's at a point where the effort put in far exceeds the tiny bit of benefit received.

 

[jasonb75]

We're not defeated that easily
Could you give us a brief description of the actual v expected on the output?

 

[Trueblue862]

Thanks Jason, I’ll do up some examples in the next couple of days and post them up. I’m not going to get time to look at it today.