Harry_1234

New Member
Joined
Aug 19, 2017
Messages
31
I have 28 tabs of data (named AB, BC, CD, EF, FG, GH, HI, IJ, JK, KL, LM, MN, NO, OP, PQ, QR, RS, ST, TU, UV, VW, WX, XY, YZ, Z00, Z01, Z02, Z03) in my spread-sheet with varying data (columns and rows are inconsistent) in each tab. I need a summary tab with count of each column per tab(only should take columns with values into consideration, ignore empty columns) and should be dynamic (i.e. if someone adds an entry into column, the summary tab should update or could click on update to reflect new count). What would be the best way to accomplish this barring pivot tables as i need the flexibility of real-time update. Attached is what i am trying to accomplish?
 

Attachments

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,012
Office Version
365
Platform
Windows
Place this code in a NEW module
Ensure that sheet "Summary" already exists
Hopefully code is all fairly self explanatory

VBA Code:
Sub Summarise()
    Dim ItemCount As Long, c As Long, ws As Worksheet
    Sheets("Summary").Cells.Clear
   
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Summary", "SheetX", "SheetY"
                'do nothing with excluded sheets
            Case Else
                If SheetHasValues(ws) Then
                    Call InsertValues("Site:", ws.Name)                                             'Insert SiteHeader
                    For c = 1 To ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
                        ItemCount = WorksheetFunction.CountA(ws.Columns(c)) - 1
                        If ItemCount > 0 Then Call InsertValues(ws.Cells(1, c), ItemCount)          'Insert count
                    Next c
                    Call InsertValues("Total DIDs:", "count what ????")
                    Call InsertValues("", "")   'gap between each site
                End If
               
        End Select
    Next ws
End Sub

Private Function SheetHasValues(ws As Worksheet) As Boolean
    Dim r As Long
    On Error Resume Next
    r = ws.Cells.Find("*", ws.Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    If r > 1 Then SheetHasValues = True Else SheetHasValues = False
    On Error GoTo 0
End Function

Private Sub InsertValues(ByVal A, B)
    Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 2) = Array(A, B)
End Sub
NOTE
Rule required to replace "count what ????"
VBA Code:
Call InsertValues("Total DIDs:", "count what ????")
summaryOut.jpg
 

Harry_1234

New Member
Joined
Aug 19, 2017
Messages
31
Place this code in a NEW module
Ensure that sheet "Summary" already exists
Hopefully code is all fairly self explanatory

VBA Code:
Sub Summarise()
    Dim ItemCount As Long, c As Long, ws As Worksheet
    Sheets("Summary").Cells.Clear
  
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Summary", "SheetX", "SheetY"
                'do nothing with excluded sheets
            Case Else
                If SheetHasValues(ws) Then
                    Call InsertValues("Site:", ws.Name)                                             'Insert SiteHeader
                    For c = 1 To ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
                        ItemCount = WorksheetFunction.CountA(ws.Columns(c)) - 1
                        If ItemCount > 0 Then Call InsertValues(ws.Cells(1, c), ItemCount)          'Insert count
                    Next c
                    Call InsertValues("Total DIDs:", "count what ????")
                    Call InsertValues("", "")   'gap between each site
                End If
              
        End Select
    Next ws
End Sub

Private Function SheetHasValues(ws As Worksheet) As Boolean
    Dim r As Long
    On Error Resume Next
    r = ws.Cells.Find("*", ws.Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    If r > 1 Then SheetHasValues = True Else SheetHasValues = False
    On Error GoTo 0
End Function

Private Sub InsertValues(ByVal A, B)
    Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 2) = Array(A, B)
End Sub
NOTE
Rule required to replace "count what ????"
VBA Code:
Call InsertValues("Total DIDs:", "count what ????")
View attachment 14810
Thank you. The "count what????" would be sum of B3 to B6 for site AB. Similarly for site BC would be the sum of B9 to B11. So, count what would sum of values for each site. How can i embed this? Also is it possible to include space between the sites? Like after Total DIDs, leave the row blank and then start with site: BC. Also, would like to add another line into code that would count all the "Total DIDs" value across all sites (AB, BC, CD, EF...etc, etc..) in the summary page and appear in the bottom as "Final Total DID's Count"
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,012
Office Version
365
Platform
Windows
it will not be those ranges every time
what is the "generic" rule to be applied?
which headers are included?
or, if a simpler condition, which headers are always excluded?

When you reply, I will post updated code to include that plus grand total
 

Harry_1234

New Member
Joined
Aug 19, 2017
Messages
31
it will not be those ranges every time
what is the "generic" rule to be applied?
which headers are included?
or, if a simpler condition, which headers are always excluded?

When you reply, I will post updated code to include that plus grand total
Right. It won't be those ranges everytime. The headers are inconsistent in each tab. But as a generic rule, on each tab/sheet any header that doesn't have any values in it will be excluded and the total will be for the headers with values in it. For example on my initially attached screen-shot, sheet AB had headers DID Ranges, DID ranges in Use and Critical Numbers with values in it. so count all those values for 3 headers and present the sum in Total DIDs.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,012
Office Version
365
Platform
Windows
summaryOut.jpg


The reason I asked about subtotals is the inconsistency in post#1
- see totals AB and BC in Summary.PNG

Amendments made:
Sub total for each site included
Grand total placed at bottom of summary
Optional variable added to InsertValues to allow blank rows between values

Delete previous code and replace with code below:
VBA Code:
Sub Summarise()
    Dim ItemCount As Long, c As Long, ws As Worksheet, SubTotal As Long, GrandTotal As Long
    Application.ScreenUpdating = False
    Sheets("Summary").Cells.Clear
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Summary", "SheetX", "SheetY"
                'do nothing with excluded sheets
            Case Else
                If SheetHasValues(ws) Then
                    SubTotal = 0
                    Call InsertValues("Site:", ws.Name, 1)                                            'Insert SiteHeader
                    For c = 1 To ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
                        ItemCount = WorksheetFunction.CountA(ws.Columns(c)) - 1
                        If ItemCount > 0 Then
                            Call InsertValues(ws.Cells(1, c), ItemCount)          'Insert count
                            SubTotal = SubTotal + ItemCount
                        End If
                    Next c
                    Call InsertValues("Total DIDs:", SubTotal)
                    GrandTotal = GrandTotal + SubTotal
                End If
        End Select
    Next ws
    Call InsertValues("GrandTotal DIDs:", GrandTotal, 1)
    Sheets("Summary").Rows("1:2").EntireRow.Delete
End Sub

Private Sub InsertValues(ByVal A, B, Optional BlankRows As Integer)
    Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1 + BlankRows).Resize(, 2) = Array(A, B)
End Sub

Private Function SheetHasValues(ws As Worksheet) As Boolean
    Dim r As Long
    On Error Resume Next
    r = ws.Cells.Find("*", ws.Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    If r > 1 Then SheetHasValues = True Else SheetHasValues = False
    On Error GoTo 0
End Function
 

Harry_1234

New Member
Joined
Aug 19, 2017
Messages
31
View attachment 15019

The reason I asked about subtotals is the inconsistency in post#1
- see totals AB and BC in Summary.PNG

Amendments made:
Sub total for each site included
Grand total placed at bottom of summary
Optional variable added to InsertValues to allow blank rows between values

Delete previous code and replace with code below:
VBA Code:
Sub Summarise()
    Dim ItemCount As Long, c As Long, ws As Worksheet, SubTotal As Long, GrandTotal As Long
    Application.ScreenUpdating = False
    Sheets("Summary").Cells.Clear
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Summary", "SheetX", "SheetY"
                'do nothing with excluded sheets
            Case Else
                If SheetHasValues(ws) Then
                    SubTotal = 0
                    Call InsertValues("Site:", ws.Name, 1)                                            'Insert SiteHeader
                    For c = 1 To ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
                        ItemCount = WorksheetFunction.CountA(ws.Columns(c)) - 1
                        If ItemCount > 0 Then
                            Call InsertValues(ws.Cells(1, c), ItemCount)          'Insert count
                            SubTotal = SubTotal + ItemCount
                        End If
                    Next c
                    Call InsertValues("Total DIDs:", SubTotal)
                    GrandTotal = GrandTotal + SubTotal
                End If
        End Select
    Next ws
    Call InsertValues("GrandTotal DIDs:", GrandTotal, 1)
    Sheets("Summary").Rows("1:2").EntireRow.Delete
End Sub

Private Sub InsertValues(ByVal A, B, Optional BlankRows As Integer)
    Sheets("Summary").Cells(Rows.Count, 1).End(xlUp).Offset(1 + BlankRows).Resize(, 2) = Array(A, B)
End Sub

Private Function SheetHasValues(ws As Worksheet) As Boolean
    Dim r As Long
    On Error Resume Next
    r = ws.Cells.Find("*", ws.Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False).Row
    If r > 1 Then SheetHasValues = True Else SheetHasValues = False
    On Error GoTo 0
End Function
My Bad on inconsistency in post#1. I tried this and got the desired results. Just curious what if i need to exclude certain columns on the sheet from being considered. Like i want to exclude CC Numbers and Server Fax Numbers from being counted from every sheet although they contain values.
 

Harry_1234

New Member
Joined
Aug 19, 2017
Messages
31
My Bad on inconsistency in post#1. I tried this and got the desired results. Just curious what if i need to exclude certain columns on the sheet from being considered. Like i want to exclude CC Numbers and Server Fax Numbers from being counted from every sheet although they contain values.
Would also like to know the other way around i.e. if i am only interested in CC Numbers and Server Fax Numbers from every sheet.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,012
Office Version
365
Platform
Windows
Time to decide what would be best for the future so that solution ...
- provides a single report of everything required
- and (if necessary) additional options for further flexibility

To avoid user confusion ... consider making numbers not in subtotals very obvious!
Items could be marked with an asterisk within current layout (but that is a bit messy) or consider alternative layouts

eg - separate column for excluded items
Summary 1.jpg


eg - different column for each site with excluded items listed in lower section
(could insert a total column for all sites in Column B)
Summary 2.jpg


Post a picture if you prefer a different layout

Q Is the same list of headers to be excluded EVERY time, or would you want flexibility to exclude headers of your choosing at time of running report?
 

Harry_1234

New Member
Joined
Aug 19, 2017
Messages
31
Time to decide what would be best for the future so that solution ...
- provides a single report of everything required
- and (if necessary) additional options for further flexibility

To avoid user confusion ... consider making numbers not in subtotals very obvious!
Items could be marked with an asterisk within current layout (but that is a bit messy) or consider alternative layouts

eg - separate column for excluded items
View attachment 15177

eg - different column for each site with excluded items listed in lower section
(could insert a total column for all sites in Column B)
View attachment 15176

Post a picture if you prefer a different layout

Q Is the same list of headers to be excluded EVERY time, or would you want flexibility to exclude headers of your choosing at time of running report?
I was going through the same confusion when I posted this. I am trying to come up with best possible solution. I think the second one would be ideal i.e. different column for each site with excluded items listed in lower section and insert a total column for all sites for both included and Excluded and Grand Total. Also, would like the flexibility to exclude headers of my choice at the time of running report. To make it easier i know what needs to be included, the excluded is what could be varying and very extensive across each site. So i am envisioning something like today i run the report i could choose CC Numbers and Server Fax Numbers to be included across each site and the rest to be excluded. The next day if i decide to include Turpik Numbers then it would be CC Numbers, Server Fax Numbers and Turpik numbers across each site and rest to be excluded below. Posting a screen-shot of the layout.
 

Attachments

Watch MrExcel Video

Forum statistics

Threads
1,099,058
Messages
5,466,328
Members
406,474
Latest member
osama beskales

This Week's Hot Topics

Top