I am trying to summarize the data in different worksheets in a workbook using VBA please.

The attached picture describes the kind of data on each worksheet and summary table gives an example of how i am trying to summarize.

In short, i am trying to summarize how often a machine goes from one mode to the other mode

Please help to come up with a VBA script for this.

Thanks and appreciate all the help.

CodePudding user response：

Count Group Changes

• Adjust the values in the constants section especially dName, the name of the destination worksheet.

Option Explicit

Sub CountTimes()
     
    ' Source
    Const sNamesList As String = "Machine #1,Machine #2"
    Const sModesList As String = "Read,Learn,Write"
    Const sTitleAddress As String = "A1"
    Const sFirstCellAddress As String = "B5"
    ' Destination
    Const dName As String = "Table"
    Const dFirstCellAddress As String = "A1"
    Const dTitleList As String = "Machine,Mode,Number of times"
    ' Workbook
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
       
    Dim sNames() As String: sNames = Split(sNamesList, ",")
    Dim sModes() As String: sModes = Split(sModesList, ",")
    Dim smCount As Long: smCount = UBound(sModes)   1
    
    Dim drCount As Long: drCount = (UBound(sNames)   1) * smCount   1
    Dim dTitles() As String: dTitles = Split(dTitleList, ",")
    Dim dcCount As Long: dcCount = UBound(dTitles)   1
    
    Dim dData As Variant: ReDim dData(1 To drCount, 1 To dcCount)
    
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = vbTextCompare
    
    Dim c As Long
    For c = 1 To dcCount
        dData(1, c) = dTitles(c - 1)
    Next c
    Dim dr As Long: dr = 1
    
    Dim sws As Worksheet
    Dim sfCell As Range
    Dim sData As Variant
    Dim sTitle As String
    Dim pMode As String ' previous
    Dim nMode As String ' next
    Dim slrow As Long
    Dim sr As Long
    
    For Each sws In wb.Worksheets(sNames)
        Set sfCell = sws.Range(sFirstCellAddress)
        slrow = sws.Cells(sws.Rows.Count, sfCell.Column).End(xlUp).Row
        sData = sfCell.Resize(slrow - sfCell.Row   1).Value
        sTitle = sws.Range(sTitleAddress)
        For sr = 1 To UBound(sData, 1)
            nMode = CStr(sData(sr, 1))
            If StrComp(nMode, pMode, vbTextCompare) <> 0 Then
                dict(nMode) = dict(nMode)   1
                pMode = nMode
            End If
        Next sr
        For c = 1 To smCount
            dr = dr   1
            dData(dr, 1) = sTitle
            pMode = sModes(c - 1)
            dData(dr, 2) = pMode
            If dict.Exists(pMode) Then
                dData(dr, 3) = dict(pMode)
            End If
        Next c
        pMode = ""
        dict.RemoveAll
    Next sws
    
    ' Write and clear below.
    Dim dws As Worksheet: Set dws = wb.Worksheets(dName)
    With dws.Range(dFirstCellAddress).Resize(, dcCount)
        .Resize(dr).Value = dData
        .Resize(dws.Rows.Count - .Row - dr   1).Offset(dr).Clear
        .Font.Bold = True
        .EntireColumn.AutoFit
    End With
    
    MsgBox "Times counted.", vbInformation
     
End Sub

CodePudding user response：

This can also be accomplished using Power Query, available in Windows Excel 2010 and Excel 365 (Windows or Mac)

To use Power Query

• Data => Get Date => From Other Sources => Blank Query
• When the PQ Editor opens, navigate to Home => Advanced Editor and paste the code below into the window that opens.
• Edit the top codelines to include all of your data ranges (either named ranges or tables) and also the line specifying the custom sort order for your modes (adding in any other modes as needed)
• Read the comments and explore the Applied Steps to understand the algorithm

M Code*

let

//Assumes each Machine is in a separate Named Range or Table
//  and the Names of the machine are the same as the named range or table
// The list could be generated automatically, but that requires more information about your setup.
    listOfMachines={"Machine1","Machine2"},

//Desired order for output of the modes
    modeOrder ={"Read","Learn","Write"},

//read in every machine list
//add the name of the machine to the start of the list
    Source = List.Generate(
        ()=>[machine=listOfMachines{0}, src=Excel.CurrentWorkbook(){[Name = listOfMachines{0}]}[Content], idx=0],
        each [idx] < List.Count(listOfMachines),
        each [machine=listOfMachines{[idx] 1}, src=Excel.CurrentWorkbook(){[Name = listOfMachines{[idx] 1}]}[Content], idx=[idx] 1],
        each Table.FromColumns({{[machine]}} & Table.ToColumns([src]),{"Machine","Time","Mode"})
    ),

//convert the list of tables into a table of tables
//then expand the tables and set the data types
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandTableColumn(#"Converted to Table", "Column1", {"Machine", "Time", "Mode"}, {"Machine", "Time", "Mode"}),
    #"Filled Down" = Table.FillDown(#"Expanded Column1",{"Machine"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Filled Down",{{"Machine", type text}, {"Time", type time}, {"Mode", type text}}),

//Group by Machine and Mode twice and generate counts for each mode,
//note the use of GroupKind.Local
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Machine", "Mode"}, {}
        ,GroupKind.Local),
    #"Grouped Rows1" = Table.Group(#"Grouped Rows", {"Machine", "Mode"}, {{"Count", each Table.RowCount(_), Int64.Type}}),

//Sort so the Modes are in the desired custom order
    #"Mode Sort Order" = Table.AddColumn(#"Grouped Rows1", "Mode Sort Order", each List.PositionOf(modeOrder,[Mode])),
    #"Sorted Rows" = Table.Sort(#"Mode Sort Order",{{"Machine", Order.Ascending}, {"Mode Sort Order", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Mode Sort Order"})
in
    #"Removed Columns"