Excel getting count using macros

Question

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

Is there a particular reason that you insist on using a macro for this? I've not tried anything, but it appears that this should reasonable to do with standard spreadsheet functions. — moreON
– moreON, Commented Jun 22, 2015 at 5:20

keong kenshih · Accepted Answer · 2015-06-23 01:49:24Z

1

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

answered Jun 23, 2015 at 1:49

keong kenshih

5123 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

keong kenshih Over a year ago

Please take note that you need add the reference : Microsoft ActiveX Data Objects 2.8 Library in order for this code to work. Go to code editor > Tools > Reference to add it

keong kenshih · Accepted Answer · 2015-06-22 05:27:20Z

0

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

answered Jun 22, 2015 at 5:27

keong kenshih

5123 silver badges8 bronze badges

2 Comments

Suresh Over a year ago

Thanks keong. However the tid is not fixed for 123 and 678. It can be anything after"tid":, within double quotes. It can be more than 2 as well

keong kenshih Over a year ago

If that was the case, you need using SQL function to get it, i will write it as another answer

Collectives™ on Stack Overflow

Excel getting count using macros

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related