How to move zero-value cells to the end of a list

[skysurfer]

Hi,

Is there a way with either built in features or with VBA to take the items in a range and sort them so all the zero values appear at the end of the list? I am trying to take a list of text strings that have some cells in the list populated with zeros and push those zero-value cells to the end of the list without changing the order of the remaining text string cells.

For example, I have this list in B10 through B15:

0
Baker
Alpha
0
Zulu
Charlie

And I would like the B10 - B15 sort to look like:

Baker
Alpha
Zulu
Charlie
0
0

Any ideas?

Lawrence

[martindwilson]

=IF(B10=0,REPT("z",10),B10) drag down then sort by that column

[skysurfer]

Martin,

Thanks for the reply.

I would really REALLY like to use a helper column, but doing so would create a complication that I'd prefer avoid. And wouldn't doing the sort also change the order of the remaining text strings? Unless I could do some kind of custom sort???

Lawrence

[martindwilson]

=IF(b10=0,"",ROW()) will leave them intact

[skysurfer]

MArtin,

I guess I'm a little dense, but that converts the zeros to blanks and the list will still have those blanks spread through the list, not moved to the end. Where am I going wrong?

Lawrence

[Marcol]

Try this helper
In C10

Please Login or Register  to view this content.

And result in D10

Please Login or Register  to view this content.

Drag/Fill both Down

[martindwilson]

you sort by the helper select b10:c15 sort ascending by col c no headers when sorting the order is numbers then/ text then /text blanks "" so the result is
in column b is
Baker
Alpha
Zulu
Charlie
0
0
column c ends up like this(but you cant see the "")
10
11
12
13
""
""

[martindwilson]

if you are going down a formula route then this bit
=IF(F10="","",IF(ISTEXT(F10),ROW(),COUNTIF(F$10:F10,0)+MATCH(LOOKUP(REPT("z",255),$B$10:$B$20),F:F,0)))
only needs
=IF(ISTEXT(F10),ROW(),ROW()+10^5)

[Marcol]

@ Martin
Hmm? ...

What exactly does Column B Row 100010, 100013, 100016, etc ... contain?
Especially if the OP might be using 2003, his/her profile doesn't say either way.

[martindwilson]

you are using small so it just moves the zeros to the end
you end up with(it doesn't matter what version of excel its just adding a number)

100010
11
12
100013
14
15

[Marcol]

@ Martin

I beg to differ ...
=INDEX(B:B,SMALL(C:C,ROWS($1:1))) using your helper will not just move the zeros to the end of the list it will knock them clean off the sheet in 2003! in later versions it will in all probability return "", instead of 0.

As I asked, what does =INDEX(B:B,100010)) return?

[Marcol]

On further thought, the solution I offered in Post #6 is incorrect ...

My apologies.

Adding a second proving Column to the data table.
It should be ...
In D10 (Helper)

Please Login or Register  to view this content.

Drag/Fill Down

Then in E10

Please Login or Register  to view this content.

Drag Across to Column F then Down as required.

See this workbook.

[watersev]

hi skysurfer, VB option without helper column

[martindwilson]

by a happy coincidence anything outside the size of the workbook rows =0 =INDEX(B:B,100010)= 0 =INDEX(B:B,10000000)=0 thats in excel 97
but since we know the length of the thing to be sorted b10:b15
you could just use

Please Login or Register  to view this content.

with (note i reversed the arguments)

Please Login or Register  to view this content.

[Marcol]

Hmm?

... option without helper column

Some b****r will soon show up with a CSE Array solution ...

[skysurfer]

You guys are amazing. Those formulaic solutions are very elegant.

I did come across a VBA solution I would like implement (so, no helper column needed), but can't figure out one little change. It moves all the blanks to the bottom of the list. What I need it to do is move the zeros to the bottom of the list...and not not just move the VALUES but the FORMULAS.

I tried changing the ".Value" to ".Formula", but it changed the order. Anyway, here is the code that works perfectly with blanks:

Please Login or Register  to view this content.

[Marcol]

@ Martin
Nice one, I'd have sworn that would never work!

@ skysurfer
Two solutions, the hard way and the easy way ...
Lucky white heather, two for the price of one ...

Go with Martin ...

For your, and public conveniance, see the attached.

[skysurfer]

Marco,

You crack me up.

Martin's solution is very elegant. But the ideal solution would be VBA and no helper columns. The list is formula derived and is surrounded by other data.

I am very close to having the VBA above work. Do you know of a way to have it move the formulas instead of the values?

Lawrence

[Marcol]

Ah ha!
Move values and formulae, now that's a different kettle of fish!

Time for you to post a sample workbook with these nasty little things, and pray for the VBaWaffen-snb to arrive one dark night ...

[skysurfer]

Okay, I've attached a file with both a working version of the macro and with a version that I'm trying to get to handle cells with formulas.

(I could not send my original file because it is huge and contains sensitive sales data.)

So, in a nut shell, I need a macro that will take items in a list (and those items are not values but formulas) and move those items that have zero value to the bottom of the list while preserving the order of the remaining items.

This:

Bravo
Alpha
0
Charlie
0

Will look like this:

Bravo
Alpha
Charlie
0
0

[skysurfer]

Thanks to Andy Pope, we now have a macro that moves zeroes to the bottom of a list when the items in the list are formulas.

(See attached)