Combine multiple row into one, keeping columns data

Question

I have the following type of table

Current table                                                   
P.no.   P.Line  ian feb mar apr mai iun iul aug sept    oct nov dec
100001  Line1   100                                         
100001  Line1                   100                         
100001  Line1                               300             
100001  Line2       200                                     
100002  Line2               100                             
100003  Line3   100                                         
100003  Line3       100                                     
100003  Line3           100                                 
100003  Line3                           300                 
100003  Line3                                   200         
100003  Line3                                               100

I need to make it look like this:

Needed result                                                   
P. no.  P.line  ian feb mar apr mai iun iul aug sept    oct nov dec
100001  Line1   100             100         300             
100001  Line2       200                                     
100002  Line2               100                             
100003  Line3   100 100 100             300     200         100

Thank you in advance.

score 0 · Answer 1 · answered Jan 17 '19 at 10:11

You can create a separate summary table and use SUMIFS. I created a table outline that looked like this:

P.no.   P.Line  ian feb mar apr mai iun iul aug sept    oct nov dec
100001 Line 1
100001 Line 2
100002 Line 2
100003 Line 3

I did this by copying A & B and using Excel's remove duplicates. This formula can be used to sum for each column (assuming your data starts in column A and ian is in C):

=SUMIFS(C$2:C$12, $A$2:$A$12, $A15, $B$2:$B$12, $B15)

This will sum column C where column A is the same as A15 and column B is the same as B15. You can drag this formula right and down and it should be dynamic.

In your example you've returned blank if there's no data, this can also be done but you'll need to wrap the above into something like this:

=IF(SUMIFS(C$2:C$12, $A$2:$A$12, $A15, $B$2:$B$12, $B15) = 0, "", SUMIFS(C$2:C$12, $A$2:$A$12, $A15, $B$2:$B$12, $B15))

This just means if it sums to 0 then return blank, otherwise give me the sum.

Joost · Answer 2 · 2019-01-23T13:41:47.877

With s as your source range and t as your target range, I would do something like this:

' loop through the rows
for i=1 to s.rows.count
    ' check if the sum code is changed
    if s(i,1) & s(i,2) <> prev_id then trow = trow +1
    ' fill the target row with the source values
    for j=1 to s.columns.count
        if not isempty(s(i,j)) then t(trow, j) = s(i,j)
    next
    ' save the sum code (for the next loop)
    prev_id = s(i,1) & s(i,2)
next

Revision: Okay, based on your link I made this:

Sub CombineRows()
    Dim rw As Long
    Dim col As Long
    Dim rng As Range

    ' make sure the cursor is somewhere in the left columns
    Set rng = Selection.CurrentRegion
    For rw = rng.Rows.Count To 2 Step -1
        If rng(rw, 2) = rng(rw - 1, 2) And rng(rw, 3) = rng(rw - 1, 3) Then
            For col = 4 To rng.Columns.Count
                If Not IsEmpty(rng(rw, col).Value2) Then
                    rng(rw - 1, col).Value2 = rng(rw, col).Value2
                End If
            Next
            rng.Rows(rw).EntireRow.Delete
        End If
    Next
End Sub

Just stand somewhere in the left three columns and run the macro.

Combine multiple row into one, keeping columns data

2 Answers2