data sort

[SQUIDD]

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

 

[Weaver]

There might be a way using formulas, but I'd use VB.

Any problem with this approach?

 

[SQUIDD]

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

 

[baitmaster]

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

 

[baitmaster]

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

and I call myself an IT professional...

 

[SQUIDD]

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

 

[Weaver]

Try this

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

 

[jasonb75]

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.

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.

 

[SQUIDD]

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

Thankyou for your help.

Dave

 

[SQUIDD]

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