Getting Rid of Zeros

[Rachel]

I have a sheet with 50 columns and 100 rows. In each row,
5 values appear in varying columns - the rest are zeros.

I want to create a new sheet with only 5 columns that
contain the 5 non-zero values for every row.

So for example, Row 2 looks something like:
0,0,0,0,23,0,0,0,0,0,0,12,0,0,2,0,0,0,0,44,0,0,0,5,0,0,0...

I would like Row 2 in my new sheet to look like:
23,12,2,44,5

I've tried advanced filters and formulas, but I am getting
a bit lost. Any help would be great!

Rachel

 

[Debra Dalgleish]

You could use a macro to do this. The following code copies the values
from sheet1 to Sheet2:

'===============================
Sub RemoveZeroCells()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

ws2.Cells.ClearContents
ws1.Range("A1").CurrentRegion.Copy _
Destination:=ws2.Range("A1")
ws2.Range("A1").CurrentRegion.Replace _
What:="0", Replacement:="=""""", _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False
ws2.Range("A1").CurrentRegion _
.SpecialCells(xlCellTypeFormulas, 23) _
.Delete Shift:=xlToLeft

End Sub
'==================================

 

[Peo Sjoblom]

One way would also be to copy and paste special as transpose on a new sheet,
then use autofilter,
custom and not equal to zero, then copy and paste special as transpose on
another sheet.
Also if the order of the numbers doesn't matter you can just sort the range
descending
that way all zeros will be to the right, then just select the columns with
zeros and delete
them

 

[Harlan Grove]

I have a sheet with 50 columns and 100 rows. In each row,
5 values appear in varying columns - the rest are zeros.

I want to create a new sheet with only 5 columns that
contain the 5 non-zero values for every row.

So for example, Row 2 looks something like:
0,0,0,0,23,0,0,0,0,0,0,12,0,0,2,0,0,0,0,44,0,0,0,5,0,0,0...

I would like Row 2 in my new sheet to look like:
23,12,2,44,5

For the heck of it, an formula solution. If the top row of your range of
original data were Original!A2:AX2, you could pull the first (leftmost) 5
nonzero entries from that record into Results!A2:E2 using the following
array formulas. Select Results!A2:E2 and enter the array formula

Results!A2:E2
=INDEX($A1:$AA1,SMALL(IF($A1:$AA1<>0,COLUMN($A1:$AA1)),{1,2,3,4,5}))

Select Results!A2:E2 and fill down as needed.

 

[Leo Heuser]

Rachel

Here's a formula solution

Assuming sheet1 contains the list in A2:AX101

In the new sheet enter this array formula in one line
in e.g. A2 (Instead of N enter 5)

=INDEX(Sheet1!$A2:$AX2,LARGE(MATCH(IF(Sheet1!$A2:$AX2<>0,Sheet1!$A2:$AX2),
IF(Sheet1!$A2:$AX2<>0,Sheet1!$A2:$AX2),0), N -(COLUMN()-COLUMN($A2))))

The formula must be entered with <Shift><Ctrl><Enter>,
also if edited later. If done correctly, Excel will display
the formula in the formula bar enclosed in curly brackets { }
Don't enter these brackets yourself.

1. Copy A2 to E2 with the fill handle (the little square in
the lower right corner of the cell)
2. Copy A2:E2 down to A101:E101 with the fill handle.

(COLUMN()-COLUMN($A2)) acts as a counter, so if
your startcell is e.g. F2 instead of A2, it must be changed
to (COLUMN()-COLUMN($F2)) etc.

The formula works for any number of non-zero values N in a
row. If you have e.g. N=11, just copy A2 to K2 in 1. above etc.

If you keep the formulae any change in Sheet1!A2:AX101
will be reflected in sheet2. If this is not necessary, then copy
A2:E101, while it's selected and choose Edit > Paste special
and choose "Values". OK and <Esc>.

 

[Harlan Grove]

original data were Original!A2:AX2, you could pull the first ....
Results!A2:E2
=INDEX($A1:$AA1,SMALL(IF($A1:$AA1<>0,COLUMN($A1:$AA1)),{1,2,3,4,5}))

....

Make that

=INDEX(Original!A2:AX2,SMALL(IF(Original!A2:AX2<>0,
COLUMN(Original!A2:AX2)),{1,2,3,4,5}))

 

[Rachel]

Help!

I tried the macro suggested by Debra, but don't have the
expertise. I got really lost...

I tried copy - paste special, but it complains that the
copy area and paste area are not the same size and shape.

I saw what you said about sort, but I would like to keep
the order, if possible. Any ideas?

Thank you,

R

 

[Rachel]

Thank you Leo,

I tried that, but getting a #N/A. I think this is because
the function was unsuccessful in finding a MATCH. I saw
this in the help menu for MATCH.

Still trying...

 

[Rachel]

Thank you again Leo,

It worked perfectly this time - could have sworn I ctrl-
shift-entered (hmmm). You have been an extremely valuable
resource!!!

Rachel

 

[Rachel]

Whoops, I spoke too soon...

It seems that I am getting zeroes (in my second sheet)
when a number other than zero appears in the first column
the original sheet. For example

1st Sheet
Row1: 0,0,0,5,0,23,9,0,0,0
Row2: 0,14,0,0,94,0,2,0,0,0
Row3: 13,0,0,0,29,0,0,37,0

2nd Sheet
Row1: 5,23,9
Row2: 14,94,2
Row3: 0,0,37 (But it should be 13,29,37)

? A bit confused. Seems to only happen when a non-zero
value is in the first column of the 1st sheet. Know you
are busy like everyone, but if you get a chance...

 

[R]

NEVERMIND - I was not inluding a first (blank) column in
the first sheet. It works now. Sorry for all the
bantering... And thank you again!!!

Me

 

[Peo Sjoblom]

Well you should just have selected a single cell on the sheet where
you want to copy it.

 

[Leo Heuser]

You're right, Rachel. The formula is flawed.
I'll get back as soon as possible with a
corrected version (I hope .
Thanks for pointing it out and sorry for the confusion.

 

[Leo Heuser]

Hi, Rachel

Try this array formula instead:

=INDEX(Sheet1!$A2:$AX2,LARGE(IF(Sheet1!$A2:$AX2<>0,
COLUMN($A2:$AX2)),5-(COLUMN()-COLUMN($A2))))

Looks a great deal like Harlan's formula

 

[Harlan Grove]

You're right, Rachel. The formula is flawed.
I'll get back as soon as possible with a
corrected version (I hope . ....
"Rachel" <[email protected]> skrev... ....

Note that Row1 and Row2 have 10 entries but Row3 has only 9.
....

I can't reproduce zeros instead of 13 and 29, but I can reproduce zero
instead of 13. What's your *EXACT* formula? Which cells is it in?

Leo's formula chokes on Row3 as follows. I'm replacing Leo's N with 3 and
assuming 1st sheet Row2 is Sheet1!A3:J3 and the leftmost result cell were
Sheet2!A3, this would be Leo's formula for Sheet2!A3.

=INDEX(Sheet1!$A3:$J3,
LARGE(MATCH(IF(Sheet1!$A3:$J3<>0,Sheet1!$A3:$J3),
IF(Sheet1!$A3:$J3<>0,Sheet1!$A3:$J3),0),
3-(COLUMN()-COLUMN($A3))))

IF(Sheet1!$A3:$J3<>0,Sheet1!$A3:$J3) evaluates to

{13,FALSE,FALSE,FALSE,29,FALSE,FALSE,37,FALSE,FALSE}

so the MATCH call evaluates to

{1,2,2,2,5,2,2,8,2,2}

Two problems. The 13 in 1st sheet Row3 corresponds to the smallest value in
the array returned by MATCH, and the zero values in Row3 correspond to
numbers returned by MATCH, so the 3rd smallest number is 2 corresponding to
zero in Row3.

I responded before in this thread, though I needed a follow-up to correct a
bug in my first formula. Here it is again, using the same assumptions as
above.

Sheet2:A3:C3
=INDEX(Sheet1!A3:J3,SMALL(IF(Sheet1!A3:J3<>0,
COLUMN(Sheet1!A3:J3)-CELL("Col",Sheet1!A3:J3)+1),{1,2,3}))

This works with the sample data above, returning

Row1: 5,23,9
Row2: 14,94,2
Row3: 13,29,37

 

[Harlan Grove]

Try this array formula instead:

=INDEX(Sheet1!$A2:$AX2,LARGE(IF(Sheet1!$A2:$AX2<>0,
COLUMN($A2:$AX2)),5-(COLUMN()-COLUMN($A2))))

...

Why LARGE(.,N-(COLUMN()-COLUMN(BaseCell)))? You could simplify your formula by
using SMALL, as in

=INDEX(Sheet1!$A2:$AX2,SMALL(IF(Sheet1!$A2:$AX2<>0,COLUMN($A2:$AX2)),
COLUMN()-COLUMN($A2)+1))

while avoiding having to enter multiple-cell array formulas, though this formula
is still an array formula, just a single-cell one. This also allows more
flexibility with respect to N. In your formula, you must enter N, and the
formula errors out when filled right into the (N+1)th cell. My alternative can
be filled right as far as needed without any editing required.

 

[Leo Heuser]

...
..

Why LARGE(.,N-(COLUMN()-COLUMN(BaseCell)))? You could simplify your formula by
using SMALL, as in

=INDEX(Sheet1!$A2:$AX2,SMALL(IF(Sheet1!$A2:$AX2<>0,COLUMN($A2:$AX2)),
COLUMN()-COLUMN($A2)+1))

while avoiding having to enter multiple-cell array formulas, though this formula
is still an array formula, just a single-cell one. This also allows more
flexibility with respect to N. In your formula, you must enter N, and the
formula errors out when filled right into the (N+1)th cell. My alternative can
be filled right as far as needed without any editing required.

The first part of your comment is not clear to me. If you're saying, that my
formula is a multiple-cell array formula, then it isn't. As for the N-part I
guess,
it's a matter of preference. I feel comfortable with the fact, that you can
get
the number by just looking at the formula, and as for the erroring out, you
apparently see it as a nuisance. I consider it a safety valve.

LeoH

 

[Harlan Grove]

. . . If you're saying, that my formula is a multiple-cell array
formula, then it isn't. . . .

Not what I meant to say. It's a single cell array formula that can be filled
right, but only into N cells (including the original).

. . . As for the N-part I guess, it's a matter of preference. I feel
comfortable with the fact, that you can get the number by just
looking at the formula, . . .

OK, but if seeing the number of matches sought in the formula is a
compelling need, you could use a multiple cell array formula like

=INDEX(Rng,SMALL(IF(Rng<>0,Rng),COLUMN(INDIRECT("C1:C"&N,0))))

Single cell array formulas have the advantage of flexibility when they don't
include hard-coded arguments - they can be filled or deleted at whim.
However, when there are hard-coded arguments, the advantages of single cell
array formulas vs multiple cell array formulas spanning ranges that happen
to be the same size as the hard-coded arguments don't seem as obvious to me.
But this may just be a matter of taste.

. . . and as for the erroring out, you apparently see it as a
nuisance. I consider it a safety valve.

To repeat, if your formula says you want, say, 5 values, then why not use a
single formula pulling 5 values. That way it's much harder to introduce
accidental errors (typos) into individual result cells. If you know you want
5 results, and there's an array formula that gives those 5 results and uses
explicit arguments (so no loss in clarity of purpose), what's the advantage
of single cell formulas?

I understand how you're using LARGE to produce an ascending order array, but
to me SMALL is the more obvious function to use for this.