Consolidating rows

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,825
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):
GroupField1Field2Field3Field4
1 aaaaaa1
1 awaaa2
1 aaaaaa3
2 dddddd5
2 eeeeee8
2 dddddd88
3 gggggg878
3 hhhhhh78
3 hhhhhh5435
<colgroup><col span="5"></colgroup><tbody> </tbody>

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

Rich (BB code):
GroupField1Field2Field3Field4
1 aaaaaa4
1 awaaa2
2 dddddd13
2 eeeeee8
3 gggggg878
3 hhhhhh5513
<colgroup><col span="5"></colgroup><tbody> </tbody>

[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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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

GroupField1Field2Field3Field4GroupField1Field2Field3Field4
1​
aaaaaa
1​
1​
aaaaaa
4​
1​
awaaa
2​
1​
awaaa
2​
1​
aaaaaa
3​
2​
dddddd
93​
2​
dddddd
5​
2​
eeeeee
8​
2​
eeeeee
8​
3​
gggggg
878​
2​
dddddd
88​
3​
hhhhhh
5513​
3​
gggggg
878​
3​
hhhhhh
78​
3​
hhhhhh
5435​

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

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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