# data sort

#### SQUIDD

##### Well-known Member
In a column I have loads of numbers like this except there are 5000 of them.

3
2
0
0
0
0
2
0
3
0
0
2
0
9

how could i sort it into another column so it stays in the same order but counts the 0 values so it would look something like this.

3
2
0 (x4)
2
0 (x1)
3
0 (x2)
2
0 (x1)
9

I know it is weird but i do have my reasons.

Thankyou

Dave

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
There might be a way using formulas, but I'd use VB.

Any problem with this approach?

thanks excelr8r

I have no problem with VB except for the fact i am no good at it.
To be honest if i sat here for the next 4 hours i would probably get there lol, but hopefully that is where you come in, 3 mins propably.
Thanks for the help in advance.

Dave

a quick loop through all the data, check if its equal to zero, count it if it is, then paste the results...

for i = 1 to 5000

hmmm... not sure where all my text went there...

and I call myself an IT professional...

Hi baitmaster

I assume by your comment you probably wrote some code that never got there, I am having a go at the moment, unsucsessfully.
Thanks

Dave

Try this

Code:
``````Sub weirdCollection()
Dim x, y, z
y = 2
z = 0
For x = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(x, 1) = 0 Then
z = z + 1
Else
If z > 0 Then
Cells(y, 2) = "0 x (" & z & ")"
z = 0
y = y + 1
Else

End If
Cells(y, 2) = Cells(x, 1)
y = y + 1
End If
Next
End Sub``````

It dumps the desired list into column B

Hi Dave,

Had a quick play with your sample list of numbers, this looks like it does the job, output is to column J, assumes list starts in row 2 with a header.

Code:
``````Sub countit()
b = 1
f = 2
For a = 2 To Cells(Rows.Count, "I").End(xlUp).Row
c = a + 1
d = Range("I" & a).Value
e = Range("I" & c).Value
If d = e Then
b = b + 1
Else:
If d = 0 Then Range("J" & f) = "0 (x" & b & ")"
b = 1
f = f + 1
End If
Next
End Sub``````

code edited, it was counting non 0 values as well, now the output is identical to sample in your original post.

Last edited:
Well done excelR8R

That is unbelievable how quick you done that
I have tested it and at first worked great.
There is just 1 problem with it.
If my list starts with a 0 or ends with a 0 then it dosent count it.also it dosent count the first number

1
3
2
0
0
4
0

would look like this

3
2
0 (x2)
4

Dave

Hi jason

Thankyou for the help.
I have just tried your code and when i run it nothing happens.
I should mention the data has no header and is in a column and not a row.

Thanks

Dave

Replies
6
Views
291
Replies
20
Views
345
Replies
4
Views
161
Replies
16
Views
480
Replies
18
Views
404

1,203,073
Messages
6,053,379
Members
444,660
Latest member
Mingalsbe

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back