Help with My Macros Program

[Guest]

Hello All,
Please be patient and read through since its a long message

Here Is the Macros i am using right now.

Sub ImportData()
'
' ImportData Macro Ctrl+I
'
Dim sTargetDate, sTargetPath, sTargetFile, sSaicID, sCraneID As String
sTargetPath = "C:\Documents and Settings\Ajay Tummala\Desktop\logs1\"

Dim rCrane As Range

TargetDate = Format(Range("'General Summary'!c2"), "yyyy-mm-dd")

'For every crane load the data
For Each rCrane In Range("'General Summary'!B5:B24")
If rCrane.Offset(0, -1).Value < 10 Then
sSaicID = "0" & rCrane.Offset(0, -1).Text
Else:
sSaicID = rCrane.Offset(0, -1).Text
End If
sCraneID = rCrane.Text
sTargetFile = "Crane-" & sSaicID & "_" & TargetDate & ".LOG"

Sheets(sCraneID).Select

'If the file exists then update the data otherwise delete the data
If Dir(sTargetPath & sTargetFile) <> "" Then
Range("'" & sCraneID & "'!A:I").Delete
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\Ajay
Tummala\Desktop\logs1\Crane-" & sSaicID & "_" & TargetDate & ".LOG",
Destination:=Range("A1"))
.Name = "Crane-" & sSaicID & "_" & TargetDate
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1)
.TextFileFixedColumnWidths = Array(8, 4)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Else
Range("'" & sCraneID & "'!A:I").Delete
End If

Next rCrane


Sheets("General Summary").Select
End Sub

-----------------

i have used this to parse data in the text files.

here is the data from the text file

00:00:22 DBG OCR crane=5 seq=5007364 camera=1 result=TRLU8631313 conf=B
00:00:22 DBG Container 1 OCR
00:00:22 DBG Discharge
00:00:31 DBG UNLOCK crane=5 seq=5007364 trolley=-4950 hoist=1340
00:00:31 DBG Discharge
00:00:31 DBG New OCR alert 39976
00:01:14 DBG LOCK crane=5 seq=5007365 type=2 trolley=9140 hoist=2850
00:01:14 DBG Lock seq 5007365 assumed to be a DISCHARGE
00:01:14 DBG GetStack ship=12 bay=3 trolley=1096 portsideto=True
00:01:14 WRN GetStack() ship not found
00:01:14 DBG Lock seq 5007365 ship=12 bay=3 stack=-1 tier=0 pairedbay=0
00:01:14 DBG OCR crane=5 seq=5007364 camera=2 result=NO OCR conf=E
00:01:48 DBG OCR crane=5 seq=5007365 camera=1 result=NYKU8328124 conf=B
00:01:48 DBG Container 1 OCR
00:01:48 DBG Discharge
00:01:50 DBG UNLOCK crane=5 seq=5007365 trolley=-4900 hoist=1320
00:01:50 DBG Discharge
00:01:50 DBG New OCR alert 39982
00:02:35 DBG LOCK crane=5 seq=5007366 type=2 trolley=9220 hoist=1890
00:02:35 DBG Lock seq 5007366 assumed to be a DISCHARGE
00:02:35 DBG GetStack ship=12 bay=3 trolley=1106 portsideto=True
00:02:35 WRN GetStack() ship not found
00:02:35 DBG Lock seq 5007366 ship=12 bay=3 stack=-1 tier=0 pairedbay=0
00:02:35 DBG OCR crane=5 seq=5007365 camera=2 result=NO OCR conf=D

Example of what is to be done:

the sequence number may be repeted but not the condidence level

00:02:35 DBG OCR crane=5 seq=5007365 camera=2 result=NO OCR conf=D
00:02:38 DBG OCR crane=5 seq=5007365 camera=1 result=NO OCR conf=C
00:02:38 DBG OCR crane=5 seq=5007365 camera=2 result=NO OCR conf=B

I dont care if its camera 1 or camera 2,

can anyone out there help me to update my marcos, so that i can capture the
conf=" " value depending on priority as A,B,C,D,E

Choose "B" in the above case.

Count all the Conf=A with are obtained from the unique "seq" Number.


i am using the following Functions in my "General Summary" sheet
1.=COUNTIF(INDIRECT("'"&$B6&"'!C:C"),"Discharge")--to count the number of
Discharges
2.=COUNTIF(INDIRECT("'"&$B6&"'!C:C"),"Load")--to count the number of Loads

I am able to count the total Number Of Conf=A, Conf=B, Etc but because the
sequence Numbers are Duplicated the count Isnt right.

Need more information I can send it thru personal mail.
(e-mail address removed)
Please Help Me out as soon as you can

 

[Guest]

Hi, I have a query. Why are the sequence numbers duplicated ? You cannot
achieve this using countif function alone. You have to write a VBA code
snippet to eliminate duplicates and then count. I hope this helps you.