 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / January 2006Count 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) > 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 [quoted text clipped - 27 lines] > -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 > [quoted text clipped - 45 lines] > > -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) > Thank you for your respons. Unfortunatly this doesn't work for me. > [quoted text clipped - 12 lines] > > > Try =COUNTA(A2:A15000)-SUMPRODUCT(--(A2:A15000<>"")/COUNTIF(A2:A15000,A2:A15000& > > "")) > > [quoted text clipped - 42 lines] > > > -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 --------------------------------------------------------------------------------------------------  SignatureKevin Backmann > 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 [quoted text clipped - 27 lines] > -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. [quoted text clipped - 82 lines] > > -0,86 44,26 39,58 54,03 > > -0,85 44,26 39,58 54,03 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.