Printable View
The principle is, for example I have these values in column:
23
23
23
0
0
23
0
0
0
0
0
9
0
7
7
0
and I want to group by number of appearances in the order of appearance, attention: whatever the number of "0" will be replaced by "0".
What must this:
3
0
1
0
1
0
2
0
First question: Are you comfortable with the VBA and the algorithm?
if so, it has a way to do it with a "buffer"
Second question: I'm curious to know the value that can have this kind of macro
If
7
7
7
2
2
0
3
3
3
Is what we will
3 or 5
2 0
0 3
3
(only the "0" that distinguishes one of the other?)
Quickly made a small code for a macro to put in a module for example (values in column A and column B result)
Code:Sub List()
Dim NB_Lines As Integer
Dim I As Integer 'Counting Lines
Dim X As Integer 'Write result
Dim Counter As Integer ' Counter values
X = 2
Counter = 0
NB_Lines = ActiveSheet().UsedRange.Rows.Count
For I = 2 To NB_Lines
If Cells(I, 1) = 0 Then
Counter = 0
Else
If Cells(I, 1) <> Cells(I - 1, 1) Then
Counter = 1
Else
Counter = Counter + 1
End If
End If
If Cells(I, 1) <> Cells(I + 1, 1) Then
Cells(X, 2) = Counter
X = X + 1
End If
Next
End Sub
@ kelfro
Compared to the first question it is true that I have been trained in C language rather than VB.
Regarding the second question, this case is in Semester 4 my project for a company. In fact I have to continue a simulator developed under excel, and which is based on the method of "lots". This method is based on the consolidation of certain entities within an order of appearance (I'm sorry it's a little complicated, but at least it proves that my question has an interest).
3 --
In the above example we have:
3
2
0
3
In fact there is also changes in lot numbers;
@ switchblade327
Thanks
It can be done by Excel formula. With the option "subtotals" but instead of choosing "money" you do pick the "number".
But a code like by switchblade327 is more "beautiful"