# Thread: Problem to combine the values of cells in the order

1. Member
Join Date
Nov 2008
Posts
1,185

## Problem to combine the values of cells in the order

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  Reply With Quote

2. Member
Join Date
Apr 2008
Posts
2,005

## Re: Problem to combine the values of cells in the order

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?)  Reply With Quote

3. Member
Join Date
May 2008
Posts
3,971

## Re: Problem to combine the values of cells in the order

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```  Reply With Quote

4. Member
Join Date
Nov 2008
Posts
1,185

## Re: Problem to combine the values of cells in the order

@ 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;

Thanks  Reply With Quote

5. Member
Join Date
Apr 2008
Posts
2,005

## Re: Problem to combine the values of cells in the order

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"  Reply With Quote

excel, combine, cell, value 