Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 16-04-2009
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  
Old 16-04-2009
Member
 
Join Date: Apr 2008
Posts: 2,001
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  
Old 16-04-2009
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  
Old 16-04-2009
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;

@ switchblade327

Thanks
Reply With Quote
  #5  
Old 16-04-2009
Member
 
Join Date: Apr 2008
Posts: 2,001
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
Reply

  TechArena Community > Software > Software Development
Tags: , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Problem to combine the values of cells in the order"
Thread Thread Starter Forum Replies Last Post
Unable to transform formulas excel cells in values Gabyr Software Development 2 05-10-2011 09:06 PM
W32 registry values are not getting matched by the default values Angrzej Networking & Security 5 19-05-2011 12:23 PM
Hiding Values and locking cells excel2007 Evoldo Windows Software 3 28-03-2011 03:32 AM
Problem in creating array from string values Cedric Software Development 4 19-01-2010 05:48 PM
Problem with cells color VB Manhamanha Software Development 5 03-06-2009 06:04 PM


All times are GMT +5.5. The time now is 12:45 AM.