I need to combine rows in spreadsheet that have common employee number.

[Rufus_Bertrand]

I'm using Microsoft Excel for Microsoft 365 MSO version 16.0.

I have a spreadsheet of employee information, I have 3 rows per employee and need to combine those rows into 1 row adding 2 columns from the 2nd and 3rd rows to the 1st row where column 'empno' matches in other rows.

The columns I want to include on 1st row are columns 'M' and 'N'.

I'm attaching file of sample data.

Any help greatly appreciated!

thanks,

Rufus

[#2]

@Rufus_Bertrand 

Here is a macro you can run. Please test on a copy of your worksheet first!

Sub CombineRows()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    m = Cells(Rows.Count, 5).End(xlUp).Row
    For r = 2 To m Step 3
        Cells(r, 15).Resize(1, 2).Value = Cells(r + 1, 13).Resize(1, 2).Value
        Cells(r, 17).Resize(1, 2).Value = Cells(r + 2, 13).Resize(1, 2).Value
    Next r
    Range("A1:R" & m).RemoveDuplicates Columns:=5, Header:=xlYes
    Application.ScreenUpdating = True
End Sub

[#3]

Hi again Hans!

Thanks for the macro!
I copied it into the test data I sent you and I'm uploading the results.
It looks like it's not getting the correct values from columns L-N?
I copied macro as delivered and didn't change anything.

Also, I searched for the 'Step 3' command in the 'For r = 2 To Step 3' line in Google and didn't find any information on it. Can you explain what this is doing?

thanks
Rufus

[#4]

Don't know if this makes a difference, but there are 3 hidden columns after 'empno' column.

thanks!

[#5]

Hi Hans,
I'm uploading file showing the desired output. Sorry I should have started with that.

thanks
Rufus

[#6]

@Rufus_Bertrand 

You wrote "The columns I want to include on 1st row are columns 'M' and 'N'"

Did you perhaps intend "The columns I want to include on 1st row are columns 'L, M' and 'N'"?

[#7]

@Rufus_Bertrand 

I see that I interpreted your original question differently. I'll take a look.

[#8]

Sorry for my foggy explanation:(

My query that created the file returns one row for each month for each employee. I would like to combine the 3 months into 1 row. Hope that sheds more light on what i'm requesting.

thanks for all your help!

[#9 · Accepted solution]

@Rufus_Bertrand 

Here is a new macro:

Sub CombineRows()
    Dim r As Long
    Dim c As Long
    Dim m As Long
    Application.ScreenUpdating = False
    ' Get the last row number
    m = Cells(Rows.Count, 5).End(xlUp).Row
    ' Loop through the rows in groups of 3
    For r = 2 To m Step 3
        ' Loop through columns 12 (L) to 14 (N)
        For c = 12 To 14
            ' Set the first of the three cells to the sum of the three
            Cells(r, c).Value = Application.Sum(Cells(r, c).Resize(3))
        Next c
    Next r
    ' Remove the duplicates
    Range("A1:R" & m).RemoveDuplicates Columns:=5, Header:=xlYes
    Application.ScreenUpdating = True
End Sub

[#10]

thanks Hans, this code works great!

Thanks for saving the day again:)

Rufus

[#11]

Hi Hans,
I just went through my data and found that some records may only have 1 or 2 rows, not 3 like I assumed.
Is there a way to modify code to go row by row and check value in column 'E' - 'empno', and if next row contains same value, copy 1st row + columns 'L' - 'N'?


thanks,
Rufus

[#12]

@Rufus_Bertrand 

Here is a new version.

Sub CombineRows()
    Dim r As Long
    Dim c As Long
    Dim m As Long
    Application.ScreenUpdating = False
    ' Get the last row number
    m = Cells(Rows.Count, 5).End(xlUp).Row
    ' Loop through the rows backwards
    For r = m - 1 To 2 Step -1
        If Cells(r + 1, 5).Value = Cells(r, 5).Value Then
            ' Loop through columns 12 (L) to 14 (N)
            For c = 12 To 14
                ' Set the first of the three cells to the sum of the three
                Cells(r, c).Value = Application.Sum(Cells(r, c).Resize(2))
            Next c
        End If
    Next r
    ' Remove the duplicates
    Range("A1:R" & m).RemoveDuplicates Columns:=5, Header:=xlYes
    Application.ScreenUpdating = True
End Sub

[#13]

Hans,
I tested your last version and it works perfectly!

I can also follow through it and understand what it is you're doing! Awesome!

Thanks! Thanks!
Rufus