CODEWITHSUNDEEP
excelvba
Suresh
Suresh

Reputation: 1101

Excel getting count using macros

I have many rows of logs like below, need to get count of 'tid' based on 'ServiceReq' from all the rows. Count for the below example for tid 123 is 5. Count for tid 678 is 2.

Can anyone help me for this using excel macros?

Example

example - sample logs in Sheet1, column A

INFO LOG Time stamp 2015-06-21-09:56 “ServiceReq”:”tid”:”123”
INFO LOG Time stamp 2015-06-21-09:56 “ServiceReq”:”tid”:”123”
INFO LOG Time stamp 2015-06-21-09:56 “ServiceReq”:”tid”:”678”
INFO LOG Time stamp 2015-06-21-09:56 “ServiceReq”:”tid”:”123”
INFO LOG Time stamp 2015-06-21-09:56 “ServiceReq”:”tid”:”123”
INFO LOG Time stamp 2015-06-21-09:56 “ServiceReq”:”tid”:”123”
EROOR LOG Time stamp 2015-06-21-09:56 “InitializeREQ”:”tid”:”abc”
EROOR LOG Time stamp 2015-06-21-09:56 “InitializeREQ”:”tid”:”abc”
EROOR LOG Time stamp 2015-06-21-09:56 “InitializeREQ”:”tid”:”abc”
EROOR LOG Time stamp 2015-06-21-09:56 “InitializeREQ”
EROOR LOG Time stamp 2015-06-21-09:56 “InitializeREQ”
EROOR LOG Time stamp 2015-06-21-09:56 “InitializeREQ”
EROOR LOG Time stamp 2015-06-21-09:56 “InitializeREQ”
EROOR LOG Time stamp 2015-06-21-09:56 “InitializeREQ”
INFO LOG Time stamp 2015-06-21-09:56 “ServiceReq”:”tid”:”678”

output expected

Servicereq tid count 123 5 678 2

Upvotes: 0

Views: 76

Answers (2)

keong kenshih
keong kenshih

Reputation: 524

Using SQL function to get the count.
You may change the code to fulfill your need.

Option Explicit
Dim WB1 As Workbook
Dim ws1 As Worksheet


Sub Test()

Dim MyConnection As ADODB.Connection
Dim MyRecord As ADODB.Recordset
Dim UnionLastRow As Long
Dim CurrentPointer As Long

Set WB1 = ThisWorkbook
Set ws1 = WB1.Worksheets("Sheet5")
Set MyConnection = New ADODB.Connection
Set MyRecord = New ADODB.Recordset

' This is the Excel 97-2003 connection string. It should also work with
' Excel 2007 onwards worksheets as long as they have less than 65536
' rows
With MyConnection
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
        "Extended Properties=Excel 8.0;"
    .Open
End With

MyRecord.Open "SELECT COUNT (*) AS MyResult FROM [Sheet5$] WHERE [Logs] LIKE '%“ServiceReq”:”tid”%' GROUP BY [Logs]", MyConnection

ws1.Cells(5, 8).CopyFromRecordset MyRecord
MyRecord.Close

MyRecord.Open "SELECT DISTINCT [Logs] FROM [Sheet5$] WHERE [Logs] LIKE '%“ServiceReq”:”tid”%' GROUP BY [Logs]", MyConnection

ws1.Cells(5, 7).CopyFromRecordset MyRecord

MyRecord.Close
MyConnection.Close

End Sub


Below is the print screen of the result:

enter image description here

Upvotes: 1

keong kenshih
keong kenshih

Reputation: 524

You may using Instr to solve your problem.

Option Explicit
Dim wb1 As Workbook
Dim ws1 As Worksheet

Sub Test()
Dim Count As Long
Dim Count2 As Long
Dim Pointer As Long
Dim TotalRow As Long
Dim LetterPosition As Long
Dim LetterPosition2 As Long

Count = 0
Set wb1 = ThisWorkbook
Set ws1 = wb1.Worksheets("Sheet5")

TotalRow = ws1.Cells(Rows.Count, "A").End(xlUp).Row

For Pointer = 1 To TotalRow
LetterPosition = InStr(ws1.Range("A" & Pointer).Value, "“ServiceReq”:”tid”:”123”")
LetterPosition2 = InStr(ws1.Range("A" & Pointer).Value, "“ServiceReq”:”tid”:”678”")
If LetterPosition > 0 Then
Count = Count + 1
LetterPosition = 0
End If

If LetterPosition2 > 0 Then
Count2 = Count2 + 1
LetterPosition2 = 0
End If

Next

ws1.Range("J2").Value = Count
ws1.Range("J3").Value = Count2

End Sub

Upvotes: 0

Related Questions