Count equal numbers in beginning of a column

I have a serie of about 10000 (or more) rows of numbers in several columns.
These numbers are from a log in a machine and there are lognumbers for every
miliseconds. Depending of how long one serie is (how many rows) there are
blocks with equal numbers. A long sequens make 32 equal blocks, a shorter one
16 and so on. I have made a test that takes out equal numbers and leave only
one left. If there are 32 blocks of equal numbers, the test deletes 31 of
them and leave the one in the middle left. This is to prevent to much data in
a workbook.

My problem is that my test depends on how many numbers are equal in every
block. I need to know if there is possible to count equal numbers from the
start of the column so I can give the result to the deleting test.

ms Log1 Log2 Log3

-1 43,09 38,43 54,76
-0,99 43,09 38,43 54,76
-0,98 43,09 38,43 54,76
-0,97 43,09 38,43 54,76
-0,96 43,09 38,43 54,76
-0,95 43,09 38,43 54,76
-0,94 43,09 38,43 54,76
-0,93 43,09 38,43 54,76
-0,92 44,26 39,58 54,03
-0,91 44,26 39,58 54,03
-0,9 44,26 39,58 54,03
-0,89 44,26 39,58 54,03
-0,88 44,26 39,58 54,03
-0,87 44,26 39,58 54,03
-0,86 44,26 39,58 54,03
-0,85 44,26 39,58 54,03

Try

=COUNTA(A2:A15000)-SUMPRODUCT(--(A2:A15000<>"")/COUNTIF(A2:A15000,A2:A15000&
""))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"FiddlerOnTheRoof" <[email protected]> wrote in
message news:[email protected]...
> I have a serie of about 10000 (or more) rows of numbers in several
columns.
> These numbers are from a log in a machine and there are lognumbers for
every
> miliseconds. Depending of how long one serie is (how many rows) there are
> blocks with equal numbers. A long sequens make 32 equal blocks, a shorter
one
> 16 and so on. I have made a test that takes out equal numbers and leave
only
> one left. If there are 32 blocks of equal numbers, the test deletes 31 of
> them and leave the one in the middle left. This is to prevent to much data
in
> a workbook.
>
> My problem is that my test depends on how many numbers are equal in every
> block. I need to know if there is possible to count equal numbers from the
> start of the column so I can give the result to the deleting test.
>
> ms Log1 Log2 Log3
>
> -1 43,09 38,43 54,76
> -0,99 43,09 38,43 54,76
> -0,98 43,09 38,43 54,76
> -0,97 43,09 38,43 54,76
> -0,96 43,09 38,43 54,76
> -0,95 43,09 38,43 54,76
> -0,94 43,09 38,43 54,76
> -0,93 43,09 38,43 54,76
> -0,92 44,26 39,58 54,03
> -0,91 44,26 39,58 54,03
> -0,9 44,26 39,58 54,03
> -0,89 44,26 39,58 54,03
> -0,88 44,26 39,58 54,03
> -0,87 44,26 39,58 54,03
> -0,86 44,26 39,58 54,03
> -0,85 44,26 39,58 54,03
>
>

Thank you for your respons. Unfortunatly this doesn't work for me.

I need a way to use code in VB to do this.
The workbook in which I run the deletion is just a temporary file. I Copy
the sheet to an other workbook when done deleting equal blocks (is much
faster than doing it inside the real file). The user of the program (using
forms) choose which file to study out of several .txt files which I call up
to open in Excel. This .txt file contain the logfiles and I need a way to
sort this out in VB. I have a way of doing it today. I count the number of
rows and depending on how many rows (number of miliseconds) I can choose my
parameters for deletion. This is not a secure way because the number of rows
is not always proportional to number of equal blocks.

Bob Phillips skrev:

> Try
>
> =COUNTA(A2:A15000)-SUMPRODUCT(--(A2:A15000<>"")/COUNTIF(A2:A15000,A2:A15000&
> ""))
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "FiddlerOnTheRoof" <[email protected]> wrote in
> message news:[email protected]...
> > I have a serie of about 10000 (or more) rows of numbers in several
> columns.
> > These numbers are from a log in a machine and there are lognumbers for
> every
> > miliseconds. Depending of how long one serie is (how many rows) there are
> > blocks with equal numbers. A long sequens make 32 equal blocks, a shorter
> one
> > 16 and so on. I have made a test that takes out equal numbers and leave
> only
> > one left. If there are 32 blocks of equal numbers, the test deletes 31 of
> > them and leave the one in the middle left. This is to prevent to much data
> in
> > a workbook.
> >
> > My problem is that my test depends on how many numbers are equal in every
> > block. I need to know if there is possible to count equal numbers from the
> > start of the column so I can give the result to the deleting test.
> >
> > ms Log1 Log2 Log3
> >
> > -1 43,09 38,43 54,76
> > -0,99 43,09 38,43 54,76
> > -0,98 43,09 38,43 54,76
> > -0,97 43,09 38,43 54,76
> > -0,96 43,09 38,43 54,76
> > -0,95 43,09 38,43 54,76
> > -0,94 43,09 38,43 54,76
> > -0,93 43,09 38,43 54,76
> > -0,92 44,26 39,58 54,03
> > -0,91 44,26 39,58 54,03
> > -0,9 44,26 39,58 54,03
> > -0,89 44,26 39,58 54,03
> > -0,88 44,26 39,58 54,03
> > -0,87 44,26 39,58 54,03
> > -0,86 44,26 39,58 54,03
> > -0,85 44,26 39,58 54,03
> >
> >
>
>
>

Then perhaps

myNum =
Activesheet.Evaluate("=COUNTA(A2:A15000)-SUMPRODUCT(--(A2:A15000<>"""")/COUN
TIF(A2:A15000,A2:A15000&""""))")

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"FiddlerOnTheRoof" <[email protected]> wrote in
message news:[email protected]...
> Thank you for your respons. Unfortunatly this doesn't work for me.
>
> I need a way to use code in VB to do this.
> The workbook in which I run the deletion is just a temporary file. I Copy
> the sheet to an other workbook when done deleting equal blocks (is much
> faster than doing it inside the real file). The user of the program (using
> forms) choose which file to study out of several .txt files which I call
up
> to open in Excel. This .txt file contain the logfiles and I need a way to
> sort this out in VB. I have a way of doing it today. I count the number of
> rows and depending on how many rows (number of miliseconds) I can choose
my
> parameters for deletion. This is not a secure way because the number of
rows
> is not always proportional to number of equal blocks.
>
> Bob Phillips skrev:
>
> > Try
> >
> >
=COUNTA(A2:A15000)-SUMPRODUCT(--(A2:A15000<>"")/COUNTIF(A2:A15000,A2:A15000&
> > ""))
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from the email address if mailing direct)
> >
> > "FiddlerOnTheRoof" <[email protected]> wrote in
> > message news:[email protected]...
> > > I have a serie of about 10000 (or more) rows of numbers in several
> > columns.
> > > These numbers are from a log in a machine and there are lognumbers for
> > every
> > > miliseconds. Depending of how long one serie is (how many rows) there
are
> > > blocks with equal numbers. A long sequens make 32 equal blocks, a
shorter
> > one
> > > 16 and so on. I have made a test that takes out equal numbers and
leave
> > only
> > > one left. If there are 32 blocks of equal numbers, the test deletes 31
of
> > > them and leave the one in the middle left. This is to prevent to much
data
> > in
> > > a workbook.
> > >
> > > My problem is that my test depends on how many numbers are equal in
every
> > > block. I need to know if there is possible to count equal numbers from
the
> > > start of the column so I can give the result to the deleting test.
> > >
> > > ms Log1 Log2 Log3
> > >
> > > -1 43,09 38,43 54,76
> > > -0,99 43,09 38,43 54,76
> > > -0,98 43,09 38,43 54,76
> > > -0,97 43,09 38,43 54,76
> > > -0,96 43,09 38,43 54,76
> > > -0,95 43,09 38,43 54,76
> > > -0,94 43,09 38,43 54,76
> > > -0,93 43,09 38,43 54,76
> > > -0,92 44,26 39,58 54,03
> > > -0,91 44,26 39,58 54,03
> > > -0,9 44,26 39,58 54,03
> > > -0,89 44,26 39,58 54,03
> > > -0,88 44,26 39,58 54,03
> > > -0,87 44,26 39,58 54,03
> > > -0,86 44,26 39,58 54,03
> > > -0,85 44,26 39,58 54,03
> > >
> > >
> >
> >
> >

I created a custom function to compare the 3 values in 2 different row, if
the values match it returns true, otherwise false.

The function is used in a procedure to determine if a row should be removed.
I marginally tested the code below and it appeared to work.

--------------------------------------------------------------------------------------------------
Function CompVals(Aval1 As Variant, Aval2 As Variant, Aval3 As _
Variant, Bval1 As Variant, Bval2 As Variant, Bval3 As Variant) As Boolean

If Aval1 = Bval1 And Aval2 = Bval2 And Aval3 = Bval3 Then
CompVals = True
Else
CompVals = False
End If

End Function
--------------------------------------------------------------------------------------------------

Sub RemoveRows()

Dim strMS As String
Dim strVal1 As String
Dim strVal2 As String
Dim strVal3 As String
Dim strVal4 As String
Dim strVal5 As String
Dim strVal6 As String
Dim blnIsMatch As Boolean
Dim lRowOffset As Long

Range("A2").Select
strMS = ActiveCell.Value

Do While strMS <> ""
strVal1 = ActiveCell.Offset(lRowOffset, 1).Value
strVal2 = ActiveCell.Offset(lRowOffset, 2).Value
strVal3 = ActiveCell.Offset(lRowOffset, 3).Value
strVal4 = ActiveCell.Offset(lRowOffset + 1, 1).Value
strVal5 = ActiveCell.Offset(lRowOffset + 1, 2).Value
strVal6 = ActiveCell.Offset(lRowOffset + 1, 3).Value
blnIsMatch = CompVals(strVal1, strVal2, strVal3, _
strVal4, strVal5, strVal6)
If blnIsMatch Then
ActiveCell.Offset(lRowOffset + 1).EntireRow.Delete
Else
ActiveCell.Offset(1).Select
lRowOffset = 0
strMS = ActiveCell.Value
End If
Loop

End Sub
--------------------------------------------------------------------------------------------------

--
Kevin Backmann


"FiddlerOnTheRoof" wrote:

> I have a serie of about 10000 (or more) rows of numbers in several columns.
> These numbers are from a log in a machine and there are lognumbers for every
> miliseconds. Depending of how long one serie is (how many rows) there are
> blocks with equal numbers. A long sequens make 32 equal blocks, a shorter one
> 16 and so on. I have made a test that takes out equal numbers and leave only
> one left. If there are 32 blocks of equal numbers, the test deletes 31 of
> them and leave the one in the middle left. This is to prevent to much data in
> a workbook.
>
> My problem is that my test depends on how many numbers are equal in every
> block. I need to know if there is possible to count equal numbers from the
> start of the column so I can give the result to the deleting test.
>
> ms Log1 Log2 Log3
>
> -1 43,09 38,43 54,76
> -0,99 43,09 38,43 54,76
> -0,98 43,09 38,43 54,76
> -0,97 43,09 38,43 54,76
> -0,96 43,09 38,43 54,76
> -0,95 43,09 38,43 54,76
> -0,94 43,09 38,43 54,76
> -0,93 43,09 38,43 54,76
> -0,92 44,26 39,58 54,03
> -0,91 44,26 39,58 54,03
> -0,9 44,26 39,58 54,03
> -0,89 44,26 39,58 54,03
> -0,88 44,26 39,58 54,03
> -0,87 44,26 39,58 54,03
> -0,86 44,26 39,58 54,03
> -0,85 44,26 39,58 54,03
>
>

Thank you both for your respons. I will try the suggestions.

Yesterday I explained this a little bit wrong. There are not 32 blocks, but
32, 16, 8, 4 or 2 equal numbers in each block. The number of numbers in each
block depends on how long the machining sequences are. Therefor I have made a
test on the lenght of the sequence so that I can use the right parameter for
deleting the redundants. Just to prevent any misunderstandings.

My thought was that I could build up a test like this:

I take the first number in the first block and store it as a variable. Then
I test the next numbers in the column and compare them with that variable. In
the end I want to return the number of equal numbers in for example the first
100 numbers in that column (must of course be at least over 32). The columns
are equal in question of how many numbers are equal in each block, therefor I
just need to do the test in one column.

My problem is just that I can't figure out how to do this?


Kevin B skrev:

> I created a custom function to compare the 3 values in 2 different row, if
> the values match it returns true, otherwise false.
>
> The function is used in a procedure to determine if a row should be removed.
> I marginally tested the code below and it appeared to work.
>
> --------------------------------------------------------------------------------------------------
> Function CompVals(Aval1 As Variant, Aval2 As Variant, Aval3 As _
> Variant, Bval1 As Variant, Bval2 As Variant, Bval3 As Variant) As Boolean
>
> If Aval1 = Bval1 And Aval2 = Bval2 And Aval3 = Bval3 Then
> CompVals = True
> Else
> CompVals = False
> End If
>
> End Function
> --------------------------------------------------------------------------------------------------
>
> Sub RemoveRows()
>
> Dim strMS As String
> Dim strVal1 As String
> Dim strVal2 As String
> Dim strVal3 As String
> Dim strVal4 As String
> Dim strVal5 As String
> Dim strVal6 As String
> Dim blnIsMatch As Boolean
> Dim lRowOffset As Long
>
> Range("A2").Select
> strMS = ActiveCell.Value
>
> Do While strMS <> ""
> strVal1 = ActiveCell.Offset(lRowOffset, 1).Value
> strVal2 = ActiveCell.Offset(lRowOffset, 2).Value
> strVal3 = ActiveCell.Offset(lRowOffset, 3).Value
> strVal4 = ActiveCell.Offset(lRowOffset + 1, 1).Value
> strVal5 = ActiveCell.Offset(lRowOffset + 1, 2).Value
> strVal6 = ActiveCell.Offset(lRowOffset + 1, 3).Value
> blnIsMatch = CompVals(strVal1, strVal2, strVal3, _
> strVal4, strVal5, strVal6)
> If blnIsMatch Then
> ActiveCell.Offset(lRowOffset + 1).EntireRow.Delete
> Else
> ActiveCell.Offset(1).Select
> lRowOffset = 0
> strMS = ActiveCell.Value
> End If
> Loop
>
> End Sub
> --------------------------------------------------------------------------------------------------
>
> --
> Kevin Backmann
>
>
> "FiddlerOnTheRoof" wrote:
>
> > I have a serie of about 10000 (or more) rows of numbers in several columns.
> > These numbers are from a log in a machine and there are lognumbers for every
> > miliseconds. Depending of how long one serie is (how many rows) there are
> > blocks with equal numbers. A long sequens make 32 equal blocks, a shorter one
> > 16 and so on. I have made a test that takes out equal numbers and leave only
> > one left. If there are 32 blocks of equal numbers, the test deletes 31 of
> > them and leave the one in the middle left. This is to prevent to much data in
> > a workbook.
> >
> > My problem is that my test depends on how many numbers are equal in every
> > block. I need to know if there is possible to count equal numbers from the
> > start of the column so I can give the result to the deleting test.
> >
> > ms Log1 Log2 Log3
> >
> > -1 43,09 38,43 54,76
> > -0,99 43,09 38,43 54,76
> > -0,98 43,09 38,43 54,76
> > -0,97 43,09 38,43 54,76
> > -0,96 43,09 38,43 54,76
> > -0,95 43,09 38,43 54,76
> > -0,94 43,09 38,43 54,76
> > -0,93 43,09 38,43 54,76
> > -0,92 44,26 39,58 54,03
> > -0,91 44,26 39,58 54,03
> > -0,9 44,26 39,58 54,03
> > -0,89 44,26 39,58 54,03
> > -0,88 44,26 39,58 54,03
> > -0,87 44,26 39,58 54,03
> > -0,86 44,26 39,58 54,03
> > -0,85 44,26 39,58 54,03
> >
> >