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

[Oraekene]

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

 

[Alex Blakenburg]

I can see how you get from the analysis sheet to the report sheet but how does the input sheet factor into the process ?

 

[Alex Blakenburg]

Give this a try:
It assumes the output sheet already exists and is empty.

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

 

[Oraekene]

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

 

[Alex Blakenburg]

Let me know how you go with the code.

 

[Oraekene]

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

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.

 

[Oraekene]

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

 

[Alex Blakenburg]

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 ?

 

[Oraekene]

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