Sum values in 1D arrays on different sheets - new sheets added annually

[bsweet0us]

There may be an easier way to do this, but I think using VBA and arrays is the best way to do this.

I have a workbook with several existing sheets and 1 new sheet added annually. All sheets are formatted the same way which is column headings in row 1. In column A is a list of names. Columns B through Y are currently empty on one sheet ("Running Totals") which is the sheet the code I'm attempting to put together will be populated.

Now for the data I'm trying to input into the "Running Totals" sheet. I have some code which compiles a list of unique names from column A in all other sheets and places that unique list in column A of "Running Totals". So far so good. Now, I need to search through all other sheets in the workbook for each name listed in column A of "Running Totals" and sum the numbers that appear in those other sheets from columns B through Y. There is a strong possibility a name will not be on one or more of the other sheets, but all names appear on at least one.

Example time!

In cell A2 of "Running Totals" we find the name Jimmy Golden. I need to find Jimmy Golden on Sheet1 and take the values from columns B through Y and store them in an array (I think). Then I need to find Jimmy Golden on Sheet2 and add the values from columns B through Y on Sheet2 to the values stored in the array from Sheet1. This iterates through all the worksheets in the workbook and places the totals of each column B through Y from the other sheets in "Running Totals" columns B through Y.

Sample data:
Sheet1 data is 3,2,4,0,0,0,1,2 (columns B through I)
Sheet2 data is 0,1,1,2,4,0,3,3 (columns B through I)
Sheet3 name is not found
Sheet4 data is 0,0,0,0,0,1,0,1 (columns B through I)

Running Totals should display the following in columns B through I: 3,3,5,2,4,1,4,6 (these values represents the sums of each number in each series)

Again, a new sheet with new data is added annually and there is no limit to the number of sheets that could be in the workbook.

I'm not opposed to taking the existing totals from "Running Totals" and only adding new sheets as they're added, but I'm not sure how easy or efficient that approach is. I'm hiding the sheets once they have been included in the totals but would like to keep the sheets in the workbook for reference if needed in the future.

I hope this is clear, but I can provide more info if needed. Thanks in advance for your help!

[Paul_Hossler]

I hope this is clear, but I can provide more info if needed. Thanks in advance for your help!

What would make it clearer is to attach a small sample workbook with before / after and/or examples, even if the 'after' is manually calculated or formulas for now

[bsweet0us]

Here is a sample workbook on a smaller scale but otherwise identical.

I am trying to get the results located on the "Running Totals" sheet.

As you can see, the names are not necessarily in the same row from sheet to sheet and may not appear on more than one sheet but could appear on all of them.

I intend to place a button on the "Running Totals" sheet to run the macro to update the totals if that's the route we go.

Hope this helps!

[Paul_Hossler]

This has some assumptions about the worksheets and number of criteria

Final formatting, etc. is left as a homework assignment

Option Explicit


Sub GenerateRunningTotals()
    Dim wsRunning As Worksheet, ws As Worksheet
    Dim rDest As Range, rRunning As Range, rRunning1 As Range
    Dim r As Long, c As Long


    Application.ScreenUpdating = False


    'init
    Set wsRunning = Worksheets("RunningTotals")
        
    wsRunning.Cells(1, 1).CurrentRegion.EntireColumn.ClearContents
    
    
    'stack the year sheets
    For Each ws In Worksheets
        If ws Is wsRunning Then GoTo NextSheet
        
        Set rDest = wsRunning.Cells(wsRunning.Rows.Count, 1).End(xlUp)
        If rDest.Row <> 1 Then Set rDest = rDest.Offset(1, 0)
        
        ws.Cells(1, 1).CurrentRegion.Copy rDest
    
NextSheet:
    Next
    
    'delete almost all headers that were copied
    Set rRunning = wsRunning.Cells(1, 1).CurrentRegion
    With rRunning
        For r = .Rows.Count To 2 Step -1
            If Len(.Cells(r, 1).Value) = 0 Then .Rows(r).EntireRow.Delete
        Next r
    End With
    
    'reset rRunning since we deleted rows
    Set rRunning = wsRunning.Cells(1, 1).CurrentRegion
    Set rRunning1 = rRunning.Cells(2, 1).Resize(rRunning.Rows.Count - 1, 1)
    
    'sort by name
    With wsRunning.Sort
        .SortFields.Clear
        .SortFields.Add Key:=rRunning1, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange rRunning
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With




    'sum into name from bottom up
    With rRunning
        For r = .Rows.Count To 3 Step -1
        
            If .Cells(r, 1).Value = .Cells(r - 1, 1).Value Then '   same name
                For c = 2 To 10     '   merge critera
                    .Cells(r - 1, c).Value = .Cells(r - 1, c).Value + .Cells(r, c).Value
                Next c
            
                .Rows(r).EntireRow.Delete
                End If
        Next r
    End With
    
    'cleanup
    wsRunning.Cells(1, 1).CurrentRegion.EntireColumn.AutoFit


    Application.ScreenUpdating = True
    
    MsgBox "Done"


End Sub

[Aussiebear]

Works like a charm Paul.

[bsweet0us]

Astounding work, Paul! I'll admit I was a bit skeptical when I saw the copy, paste, delete section but it does indeed work like a charm. I modified it a bit to fit my needs and would never have thought to attack the problem this way. Again many thanks!

[snb]

In VBA:
Avoid any interaction with the Workbook (reading/writing) as much as possible; use the RAM instead.

Sub M_snb()
  With CreateObject("scripting.dictionary")
    For Each it In Sheets(Array("2019", "2020", "2021"))
      sn = it.UsedRange
      For j = 2 To UBound(sn)
        sq = Application.Index(sn, j)
        If .exists(sn(j, 1)) Then
          sq = .Item(sn(j, 1))
          For jj = 2 To UBound(sn, 2)
            sq(jj) = sq(jj) + sn(j, jj)
          Next
        End If
        .Item(sn(j, 1)) = sq
      Next
    Next
      
    Sheet1.Cells(20, 1).Resize(.Count, UBound(sn, 2)) = Application.Index(.items, 0, 0)
  End With
End Sub

In Excel
In sheet Running Totals, Cell B2:

=SUMPRODUCT(('2019'!$A$2:$A$5=$A2)*'2019'!B$2:B$5+('2020'!$A$2:$A$5=$A2)*'2020'!B$2:B$5+('2021'!$A$2:$A$5=$A2)*'2021'!B$2:B$5) 

[bsweet0us]

Thanks for an alternate solution, snb, but my immediate concern is that this code doesn't appear to be "future-proof". By that I mean another worksheet will be added this year and it would have to be included in the initial sheets array, then again the following year, and so on. I don't intend to be the curator of this for eternity, so I want it to be as plug-and-play as possible.

This approach is similar to what I envisioned when I started this topic, but unless you can convince me it will automatically populate the new sheets as they're added I don't think this approach will fit my needs exactly. If I am wrong in this assumption, please point out the flaws in my reasoning so I can become more adept at deciphering the code.

THANKS!

[snb]

If you intend the workbook to be future-proof you should design it as future-proof.
That means : alll the data should go in 1 worksheet (not split into separate workbooks pro annum).
If you want to use Excel as a database you should treat the data as a database.
Preferably inserted as a dynamci Table (Listobject in VBA).
The only extra you need is a pivottable (in the same, but preferably in another worksheet).
You won't need 'adaptive VBA-code' or 'adaptive Excel formulae'.
The builltin options offer you everything you need, provided your data storage has been done correctly.

In short: the use of formulae or VBA in this case is only to amend a wrong stucture of data.

And of course in the VBA code

     For Each it In Sheets
       If len(it.name) = 4 then
        sn = it.UsedRange
        ----
      end if

[Paul_Hossler]

@bsweet)us - there are ways to make my little macro more efficient and more future proof

I decided to go with a very simple approach (copy / paste / delete) since IMHO performance wasn't even close to being an issue

If I were going to make it 'future-proof' I'd combine the year work sheets into a single pivot table friendly list, and then just do a PT from the list (snb's database)

A more efficient but less user friendly (what ever that means) way would be a long database like list of ListObject with

Name -- Year -- Criteria -- Count

as columns

Again IMO a system / process has a data layer, a computation layer, and a presentation layer. Life gets complicated with someone tries to to do too much all at once

List worksheet
Pivot Table worksheet
Formatted PT worksheet

[bsweet0us]

I'm not opposed to a different formatting option for the data and computation, but not only does this workbook need to be as future-proof as possible, but also idiot-proof. I'm minorly efficient at Excel and VBA, but I'm light years behind many of those here and the ones who will eventually be utilizing this workbook as probably as far behind me as I am behind all of you.

If pivot tables/dynamic tables/whatever can be picked up quickly by an Excel novice, I'd be interested in seeing how it would be implemented but adding a sheet, inputting data, and then processing that data is something almost anybody can do with even a modicum of experience with Excel.

I love to learn new things and new ways to approach problems in Excel, but I also know my current limitations and the limitations of those after me!

[Paul_Hossler]

If you're interested, this is a different approach

The annual sheets are still 2D, but the macro makes a 1D pivot table friendly list, and then refreshes the pivot table

Using a PT might give you more future proofing and some data analysis and report formatting capability

A 'pure' database approach would skip the annual worksheets and just use the TempList, BUT sometimes that's not very user friendly

Option Explicit


Sub GenerateRunningTotals()
    Dim wsTempList As Worksheet, ws As Worksheet
    Dim rData As Range
    Dim r As Long, c As Long, o As Long


    Application.ScreenUpdating = False


    'init
    Set wsTempList = Worksheets("TempList")
        
    o = 1
    With wsTempList
        .Cells(1, 1).CurrentRegion.EntireColumn.ClearContents
        .Cells(o, 1).Value = "Year"
        .Cells(o, 2).Value = "Name"
        .Cells(o, 3).Value = "Criteria"
        .Cells(o, 4).Value = "Amount"
    End With
    
    o = o + 1
    
    'stack the year sheets
    For Each ws In Worksheets
        With ws
            If ws Is wsTempList Then GoTo NextSheet
            If Not .Name Like "####" Then GoTo NextSheet
            
            Set rData = .Cells(1, 1).CurrentRegion
            
            For r = 2 To rData.Rows.Count
                For c = 2 To rData.Columns.Count
                    If rData.Cells(r, c).Value > 0 Then
                        wsTempList.Cells(o, 1).Value = .Name
                        wsTempList.Cells(o, 2).Value = rData.Cells(r, 1).Value
                        wsTempList.Cells(o, 3).Value = rData.Cells(1, c).Value
                        wsTempList.Cells(o, 4).Value = rData.Cells(r, c).Value
                        
                        o = o + 1
                    End If
                Next c
            Next r
        End With
NextSheet:
    Next
    
    wsTempList.Cells(1, 1).CurrentRegion.Name = "DataList"
    
    Worksheets("RunningTotals").PivotTables(1).PivotCache.Refresh


    Application.ScreenUpdating = True
    
    MsgBox "Done"


End Sub

[bsweet0us]

I'm not very familiar with Pivot Tables honestly, but picking through the code and watching what it does, I think I mostly understand the process. My biggest concern would be the "TempList" worksheet and it filling quickly. In my actual workbook I have 25 different "criteria" and currently in excess of 300 unique names. Now, there is not a non-zero value in all criteria for every name, but is it possible this approach with storing all the criteria for each name on one sheet becomes unsustainable? Also, is there a way to include a "Grand Total" column (as is there now) but the values in that column are a formula? I ask because each criteria is assigned a value and what we are inputting is the number of times said criteria is achieved.

[snb]

See

[Paul_Hossler]

TempList can 1M+ rows and 25 x 300 = 7,500 per year, so for 10 years = 75,000 so I think you'll be OK. If the data = 0 then I didn't store it

As for the rest, we'd have to see a more complete example with criteria values. etc.

If there's a LOT of data on each annual, then it might be worthwhile to load in into 2D arrays and build the TempList from them

PTs have a lot of capability (formatting and calculating) and there's no sense in recreating something

[bsweet0us]

I am going to preface this reply with, "Don't hate on the sheet as it is presented." I took it over several years ago and am now trying to create something that can be useful for the future.

The workbook compiles data from our local bowling association to determine objective qualifications for induction into the local Hall of Fame. We have established the criteria that runs along row 1 and the associated points with each accomplishment beneath each in parenthesis. Column Z currently has a formula that calculates the earned points.

The data from past years will not change but I would like to maintain the data for reference purposes.

I realized from my initial post that the data entry for the names was different in years past, but I think I remedied that issue in the code.

Very rudimentary, I know, but the desired result is correct.

Feel free to pick it apart and show me the error of my ways!

[Paul_Hossler]

Really not sure where you're going with this

I see 5 sheets with data from 2017 to 2021-- where's 2022 data go?

1. I formatted the 5 sheets the same

2. Put the points in a separate cell

3. Use SUMPRODUCT() in col Z

4. To avoid typos, extra space, trailing space, etc. I added a 'Master' ws with the points and criteria

5. Did not mess with the macro, since I didn't see 2022 data

Capture.JPG

[bsweet0us]

1. We haven't populated data past what is in the workbook to this point. A new sheet will be added for the next year's data.

The previously attached workbook generates a template when a new sheet is added that mirrors the "Running Totals" worksheet. The data is then filled in on the newly added sheet for each bowler who achieved any of the categories in Row 1. The "Running Totals" sheet then iterates through all the worksheets and generates a new list of unique names (the only names added to this unique list would be names that are new to the most recently created worksheet). It then collates the data for each bowler in each category and places the totals in the "Running Totals" worksheet. Column Z then generates the total points earned.

I'm certain there is a better way to input the data year over year, but not sure how. The new sheet with the prefilled names is to minimize any spelling errors that could easily crop up with human data entry and would cause incorrect calculations. I'm open to suggestions for a better way to import data year over year.

[snb]

A full answer to all your questions in #14.

[bsweet0us]

A full answer to all your questions in #14.

I'm trying to piece together the answers from the attached workbook in #14 but I'd like to know where the data in Columns B-D came from. Did you input that data manually or was it generated from the sample workbook I uploaded?

If I were to use this approach, how would you recommend gathering the data for 25 categories and potentially hundreds of people?

I really want to learn how to generate this data more efficiently, but I need some help in how you put together what you attached.

THANKS!