CODEWITHSUNDEEP
excelexcel-formula
TylerNG
TylerNG

Reputation: 941

delete 0 and restack in excel

I have this issue in excel where I want to delete 0 and re-stack the rows.

Problem:

0 0 1 2 3
0 0 0 1 0
0 2 3 0 1
2 5 3 0 0

The desired result would be 

1 2 3
1 0
2 3 0 1
2 5 3 0 0

Any suggestions?

Upvotes: 0

Views: 66

Answers (2)

JvdV
JvdV

Reputation: 75870

Just for the sake of giving alternatives:

enter image description here

Formula in A6 translates to:

=IFERROR(INDEX($A1:$E1,,MATCH(TRUE,INDEX($A1:$E1>0,0),0)+COLUMN()-1),"")

Dragged down and sideways.

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152505

This will create a range from the first non 0 to the end and then the outer INDEX will return them in order as it is dragged across.

=IFERROR(INDEX(INDEX($A1:$E1,AGGREGATE(15,7,COLUMN($A1:$E1)/($A1:$E1<>0),1)):$E1,,COLUMN(A:A)),"")

enter image description here

Upvotes: 2

Related Questions