Macro to scan multiple columns and find total sum of each unique item on a list of unique values

Oraekene

New Member
Joined
Sep 20, 2022
Messages
46
Office Version
  1. 2013
Platform
  1. Windows
Good day. Been struggling with this and would appreciate some help. I'd like to write a macro that goes through multiple columns in a sheet and sums the total values of each item beside it's cell in a unique list. Similar to how to find for eg. sum of total sales for multiple months for a particular product. I have attached a sample sheet to explain. The update sheets are to show how the input data worksheet would expand over time, so the code would need to work for a dynamic range. So far working on it i've found i might need a SUMIF INDEX-MATCH array function, but i don't know how to write it. I use Excel 2013.

Would be grateful for any help

Here is the sample sheet: Sample sheet
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
I can see how you get from the analysis sheet to the report sheet but how does the input sheet factor into the process ?
 
Upvote 0
Give this a try:
It assumes the output sheet already exists and is empty.

VBA Code:
Sub SumUniqueItems()

    Dim rngAnalysis As Range, arrAnalysis As Variant
    Dim shtAnalysis As Worksheet, shtReport As Worksheet
    Dim rngReport As Range
    Dim lrowAnalysis As Long, lrowReport As Long, i As Long, j As Long
    
    Set shtAnalysis = Worksheets("analysis updated 2")                      ' <--- Change to data source sheet name
    Set rngAnalysis = shtAnalysis.Range("A1").CurrentRegion
    arrAnalysis = rngAnalysis.Value

    Set shtReport = Worksheets("report updated 2")                          ' <--- Change to output sheet name
    Set rngReport = shtReport.Range("A1")
    arrReport = rngReport.Value
    
    Dim dictAnalysis As Object, dictKey As String

    Set dictAnalysis = CreateObject("Scripting.dictionary")
    
    ' Load Analysis range into Dictionary & Sum rows with matching criteria
    For i = 1 To UBound(arrAnalysis) Step 1
        For j = 1 To UBound(arrAnalysis, 2) Step 2
            If arrAnalysis(i, j) <> "" Then
                dictKey = arrAnalysis(i, j)
                If Not dictAnalysis.exists(dictKey) Then
                    dictAnalysis(dictKey) = arrAnalysis(i, j + 1)
                Else
                     dictAnalysis(dictKey) = dictAnalysis(dictKey) + arrAnalysis(i, j + 1)
                End If
            End If
        Next j
    Next i
    
    ' Write back Totals
    rngReport.Resize(dictAnalysis.Count).Value = Application.Transpose(dictAnalysis.keys)
    rngReport.Resize(dictAnalysis.Count).Offset(0, 1).Value = Application.Transpose(dictAnalysis.items)

End Sub
 
Upvote 0
Solution
I can see how you get from the analysis sheet to the report sheet but how does the input sheet factor into the process ?
Hi! It was part of the sample data for an earlier task i was trying to solve, and now have. The analysis sheet are columns of unique values generated from the input sheet. That input sheet would be generated from the same column in multiple worksheets (eg. Column C in sheet1, sheet2, sheet3, etc). Each column would have duplicate values so analysis sheet is after those duplicate values have been cleaned out
 
Upvote 0
Let me know how you go with the code.
Really sorry about how i am so late with this reply. Really really sorry. Had to stop working on the project for q while to deal with a ill family member. Go back to it, tested this yesterday and it worked!!!! Thank you so much! I've tagged it as the solution
 
Upvote 0
Let me know how you go with the code.
Thank you so much for the answer! It created a unique list, adds up the sum for each value and then sorts them un order from least to most. Its amazing! Thank you again

I don't want to sound ungrateful but can i ask for a modification? The original idea was to rank based on the sum of the row numbers, but then i realised i need to measure central tendency which would be an Average or Mean, not Sum. Also i wanted to trim off outliers row numbers, say top 5% and bottom 5% of the numbers, and then find an average from the rest. Basically a trimmean. Tried to modify the code to do this but i have no idea how. Help would be appreciated
 
Upvote 0
This should be a new thread as it is a different question.
If you want to post a link to it here I can take a look to see if it something I can help with.
You will need to explain the calculation you want. Is the calculation trimming off at the row level or are the codes being totalled first and the trimming happens as the total level.
How would you do it in Excel directly ?
 
Upvote 0
This should be a new thread as it is a different question.
If you want to post a link to it here I can take a look to see if it something I can help with.
You will need to explain the calculation you want. Is the calculation trimming off at the row level or are the codes being totalled first and the trimming happens as the total level.
How would you do it in Excel directly ?
Okay i'll get on this immediately and share the link here
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top