Combine rows and sum values ​​in a worksheet

Question

I have an excel sheet with the data below (pipe "|" to delimit the columns).

A|B|C|X|50|60 D|E|F|X|40|30 A|B|C|X|10|20 A|B|C|Y|20|20 A|B|C|X|20|70 D|E|F|X|10|50 A|B|C|Y|10|10 

The result that I am trying to get is:

 A|B|C|X|80|150 A|B|C|Y|30|30 D|E|F|X|50|80 

Values ​​A, B, C and D, E, F are unique identifiers. In fact, only A or D can be considered. The values ​​of X and Y are similar to "types", and integer values ​​are sums. This pattern has been simplified; there are thousands of unique identifiers, dozens of types, and dozens of values. Lines are not sorted; types can be located in higher or lower rows. I try to avoid using a pivot table.

 Dim LastRow As Integer Dim LastCol As Integer Dim i As Integer LastCol = Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Column LastRow = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row For i = 1 To LastRow ???? Next i 

The above code gets to the loop point through the lines, but I do not understand what is after this point.

Answer 1

• Sort them in all alphabetical columns that you consider important.

• In the unused column on the right, use the following formula in the second row:

  = IF ($ A2 & $ B2 & $ C2 & $ D2 = $ A3 & $ B3 & $ C3 & $ D3, "", SUMIFS (E: E, $ A: $ A, $ A2, $ B: $ B , $ B2, $ C: $ C, $ C2, $ D: $ D, $ D2))

• Copy this formula to the right of one column, then fill in both columns until your data

• Filter by two columns, removing spaces.

  

• If necessary, copy the data to a new report worksheet and delete columns E and F.

Addendum:

A more automatic approach can be achieved using some form of array and some simple mathematical operations. I selected a dictionary object to use its indexed key to recognize patterns in the first four alphabetic identifiers.

To use the scripting dictionary, you need to go to VBE Tools ► Links and add Microsoft Scripting script runtime. The following code will not compile without it.

For dynamic columns of keys and integers, the following has been adjusted.

 Sub rad_collection() Dim rw As Long, nc As Long, sTMP As String, v As Long, vTMP As Variant Dim i As Long, iNumKeys As Long, iNumInts As Long Dim dRADs As New Scripting.Dictionary dRADs.CompareMode = vbTextCompare iNumKeys = 5 'possibly calculated by num text (see below) iNumInts = 2 'possibly calculated by num ints (see below) With ThisWorkbook.Sheets("Sheet4").Cells(1, 1).CurrentRegion 'iNumKeys = Application.CountA(.Rows(2)) - Application.Count(.Rows(2)) 'alternate count of txts 'iNumInts = Application.Count(.Rows(2)) 'alternate count of ints For rw = 2 To .Cells(Rows.Count, 1).End(xlUp).row vTMP = .Cells(rw, 1).Resize(1, iNumKeys).Value2 sTMP = Join(Application.Index(vTMP, 1, 0), Chr(183)) If Not dRADs.Exists(sTMP) Then dRADs.Add Key:=sTMP, Item:=Join(Application.Index(.Cells(rw, iNumKeys + 1).Resize(1, iNumInts).Value2, 1, 0), Chr(183)) Else vTMP = Split(dRADs.Item(sTMP), Chr(183)) For v = LBound(vTMP) To UBound(vTMP) vTMP(v) = vTMP(v) + .Cells(rw, iNumKeys + 1 + v).Value2 Next v dRADs.Item(sTMP) = Join(vTMP, Chr(183)) End If Next rw rw = 1 nc = iNumKeys + iNumInts + 1 .Cells(rw, nc + 1).CurrentRegion.ClearContents 'clear previous .Cells(rw, nc + 1).Resize(1, nc - 1) = .Cells(rw, 1).Resize(1, nc - 1).Value2 For Each vTMP In dRADs.Keys 'Debug.Print vTMP & "|" & dRADs.Item(vTMP) rw = rw + 1 .Cells(rw, nc + 1).Resize(1, iNumKeys) = Split(vTMP, Chr(183)) .Cells(rw, nc + iNumKeys + 1).Resize(1, iNumInts) = Split(dRADs.Item(vTMP), Chr(183)) .Cells(rw, nc + iNumKeys + 1).Resize(1, iNumInts) = _ .Cells(rw, nc + iNumKeys + 1).Resize(1, iNumInts).Value2 Next vTMP End With dRADs.RemoveAll: Set dRADs = Nothing End Sub 

Just run the macro against the numbers you provided as samples. I suggested some form of column heading headers in the first row. The dictionary object is populated, and duplicates in the combined identifiers are added up. It remains only to break them into backups and return them to the worksheet in an unused area.

Location of the Microsoft Scripting runtime. In the Visual Basic editor (aka VBE), select Tools ► Links ( Alt + T , R ) and scroll down a little more than halfway to find it.