Consolidating rows

T

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,924
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I am trying to sum rows based on some criteria.
(The Group column is only here to explain that the rows come in "3s").

This is the data:


Rich (BB code): 
[TABLE="width: 225"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD]Group[/TD]
[TD]Field1[/TD]
[TD]Field2[/TD]
[TD]Field3[/TD]
[TD]Field4[/TD]
[/TR]
[TR]
[TD="align: right"]1   [/TD]
[TD]a[/TD]
[TD]aa[/TD]
[TD]aaa[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1   [/TD]
[TD]a[/TD]
[TD]w[/TD]
[TD]aaa[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]1   [/TD]
[TD]a[/TD]
[TD]aa[/TD]
[TD]aaa[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]2   [/TD]
[TD]d[/TD]
[TD]dd[/TD]
[TD]ddd[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]2   [/TD]
[TD]e[/TD]
[TD]ee[/TD]
[TD]eee[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]2   [/TD]
[TD]d[/TD]
[TD]dd[/TD]
[TD]ddd[/TD]
[TD="align: right"]88[/TD]
[/TR]
[TR]
[TD="align: right"]3   [/TD]
[TD]g[/TD]
[TD]gg[/TD]
[TD]ggg[/TD]
[TD="align: right"]878[/TD]
[/TR]
[TR]
[TD="align: right"]3   [/TD]
[TD]h[/TD]
[TD]hh[/TD]
[TD]hhh[/TD]
[TD="align: right"]78[/TD]
[/TR]
[TR]
[TD="align: right"]3   [/TD]
[TD]h[/TD]
[TD]hh[/TD]
[TD]hhh[/TD]
[TD="align: right"]5435[/TD]
[/TR]
</tbody>[/TABLE]

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]I am trying to get this result:

Rich (BB code): 
[TABLE="width: 225"]
<colgroup><col span="5"></colgroup><tbody>[TR]
[TD]Group[/TD]
[TD]Field1[/TD]
[TD]Field2[/TD]
[TD]Field3[/TD]
[TD]Field4[/TD]
[/TR]
[TR]
[TD="align: right"]1   [/TD]
[TD]a[/TD]
[TD]aa[/TD]
[TD]aaa[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]1   [/TD]
[TD]a[/TD]
[TD]w[/TD]
[TD]aaa[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2   [/TD]
[TD]d[/TD]
[TD]dd[/TD]
[TD]ddd[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD="align: right"]2   [/TD]
[TD]e[/TD]
[TD]ee[/TD]
[TD]eee[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]3   [/TD]
[TD]g[/TD]
[TD]gg[/TD]
[TD]ggg[/TD]
[TD="align: right"]878[/TD]
[/TR]
[TR]
[TD="align: right"]3   [/TD]
[TD]h[/TD]
[TD]hh[/TD]
[TD]hhh[/TD]
[TD="align: right"]5513

[/TD]
[/TR]
</tbody>[/TABLE]

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]because if Field1, Field2 and Field3 match, then sum Field4 (but only within the same group).

I have tried this code:

Rich (BB code): 
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Dim DataArray() As Variant
    
    DataArray() = Cells(1, 1).CurrentRegion.Value
    
    Dim OutputArray(1 To 10, 1 To 4) As Variant
    
    Dim i
    
    Dim j
    
    Dim k
    
    k = 1
    
    For i = 2 To 10
      
            If DataArray(i, 1) = DataArray(i + 1, 1) And _
               DataArray(i, 2) = DataArray(i + 1, 2) And _
               DataArray(i, 3) = DataArray(i + 1, 3) Then
        
                For j = 1 To 3
                
                    OutputArray(k, j) = DataArray(i, j)
        
                Next j
            
                OutputArray(k, 4) = DataArray(i, 4) + DataArray(i + 1, 4)
                
                If DataArray(i, 1) = DataArray(i + 2, 1) And _
                   DataArray(i, 2) = DataArray(i + 2, 2) And _
                   DataArray(i, 3) = DataArray(i + 2, 3) Then
                
                    OutputArray(k, 4) = OutputArray(k, 4) + DataArray(i + 2, 4)
                    
                    i = i + 2
                    
                Else
                
                    i = i + 1
                    
                End If
              
            Else
            
                If DataArray(i, 1) = DataArray(i + 2, 1) And _
                   DataArray(i, 2) = DataArray(i + 2, 2) And _
                   DataArray(i, 3) = DataArray(i + 2, 3) Then
            
                
                    For j = 1 To 3
            
                        OutputArray(k, j) = DataArray(i, j)
                        
                    Next j
                    
                    OutputArray(k, 4) = DataArray(i, 4) + DataArray(i + 2, 4)
                    
                    
                
                Else
                
                    For j = 1 To 4
            
                        OutputArray(k, j) = DataArray(i, j)
                        
                    Next j
                
                End If
                
            End If
        
        k = k + 1
        
    Next i[/FONT]
[/FONT]


but it doesn't work because if rows 1 and 3 are the same, they are summed (as it should) and row 2 also gets "picked" up by OutputArray but row 3 gets "counted" again.

How can I amend it to get it to work?

Thanks


[/FONT]<strike>
</strike>[/FONT]<strike></strike>
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
it must be a vba?

and you did mistake in the result, should be 93 not 13
 
Last edited:
Upvote 0
What about something along these lines?


Excel 2010
ABCDE
1GroupField1Field2Field3Field4
21aaaaaa1
31awaaa2
41aaaaaa3
52dddddd5
62eeeeee8
72dddddd88
83gggggg878
93hhhhhh78
103hhhhhh5435
11
12aaaaaa4
13awaaa2
14dddddd93
15eeeeee8
16gggggg878
17hhhhhh5513
Sheet13
Cell Formulas
RangeFormula
E12=SUMIFS($E$2:$E$10,$B$2:$B$10,B12,$C$2:$C$10,C12,$D$2:$D$10,D12)
 
Upvote 0
Preferably vba but if that's too difficult, sumifs will do.

Thanks
 
Upvote 0
another way with PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Group[/td][td=bgcolor:#5B9BD5]Field1[/td][td=bgcolor:#5B9BD5]Field2[/td][td=bgcolor:#5B9BD5]Field3[/td][td=bgcolor:#5B9BD5]Field4[/td][td][/td][td=bgcolor:#70AD47]Group[/td][td=bgcolor:#70AD47]Field1[/td][td=bgcolor:#70AD47]Field2[/td][td=bgcolor:#70AD47]Field3[/td][td=bgcolor:#70AD47]Field4[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]a[/td][td=bgcolor:#DDEBF7]aa[/td][td=bgcolor:#DDEBF7]aaa[/td][td=bgcolor:#DDEBF7]
1​
[/td][td][/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]a[/td][td=bgcolor:#E2EFDA]aa[/td][td=bgcolor:#E2EFDA]aaa[/td][td=bgcolor:#E2EFDA]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1​
[/td][td]a[/td][td]w[/td][td]aaa[/td][td]
2​
[/td][td][/td][td]
1​
[/td][td]a[/td][td]w[/td][td]aaa[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]a[/td][td=bgcolor:#DDEBF7]aa[/td][td=bgcolor:#DDEBF7]aaa[/td][td=bgcolor:#DDEBF7]
3​
[/td][td][/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]d[/td][td=bgcolor:#E2EFDA]dd[/td][td=bgcolor:#E2EFDA]ddd[/td][td=bgcolor:#E2EFDA]
93​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
2​
[/td][td]d[/td][td]dd[/td][td]ddd[/td][td]
5​
[/td][td][/td][td]
2​
[/td][td]e[/td][td]ee[/td][td]eee[/td][td]
8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
2​
[/td][td=bgcolor:#DDEBF7]e[/td][td=bgcolor:#DDEBF7]ee[/td][td=bgcolor:#DDEBF7]eee[/td][td=bgcolor:#DDEBF7]
8​
[/td][td][/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA]g[/td][td=bgcolor:#E2EFDA]gg[/td][td=bgcolor:#E2EFDA]ggg[/td][td=bgcolor:#E2EFDA]
878​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
2​
[/td][td]d[/td][td]dd[/td][td]ddd[/td][td]
88​
[/td][td][/td][td]
3​
[/td][td]h[/td][td]hh[/td][td]hhh[/td][td]
5513​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
3​
[/td][td=bgcolor:#DDEBF7]g[/td][td=bgcolor:#DDEBF7]gg[/td][td=bgcolor:#DDEBF7]ggg[/td][td=bgcolor:#DDEBF7]
878​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
3​
[/td][td]h[/td][td]hh[/td][td]hhh[/td][td]
78​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
3​
[/td][td=bgcolor:#DDEBF7]h[/td][td=bgcolor:#DDEBF7]hh[/td][td=bgcolor:#DDEBF7]hhh[/td][td=bgcolor:#DDEBF7]
5435​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Code: 
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Group", "Field1", "Field2", "Field3"}, {{"Field4", each List.Sum([Field4]), type number}})
in
    Group[/SIZE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Need help modify to pick any first number of my choice
Replies
6
Views
600
motilulla
M
G
  • Question Question
Why does #VALUE error in VBA function occur when creating a ReDim Matrix of Stock prices from a Named Range?
Replies
2
Views
88
Peter_SSs
Peter_SSs
K
  • Question Question
How to shorten vba code without any problems
Replies
1
Views
279
kamranyd
K
A
  • Solved
Why isn't this code working
Replies
5
Views
534
amoverton2
A
S
  • Solved
Sort within rows in same column
Replies
2
Views
143
sharshra
S

Forum statistics

Threads
1,223,893
Messages
6,175,246
Members
452,623
Latest member
cliftonhandyman

Share this page

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