Forum Discussion

Rufus_Bertrand's avatar
Rufus_Bertrand
Brass Contributor
Sep 28, 2021
Solved

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

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 f...
excel
  • HansVogelaar's avatar
    HansVogelaar
    Sep 29, 2021

    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
HansVogelaar's avatar
HansVogelaar
MVP
Sep 29, 2021

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
  • Rufus_Bertrand's avatar
    Rufus_Bertrand
    Brass Contributor
    Sep 29, 2021
    thanks Hans, this code works great!

    Thanks for saving the day again:)

    Rufus
    • Rufus_Bertrand's avatar
      Rufus_Bertrand
      Brass Contributor
      Oct 01, 2021

      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

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Oct 01, 2021

        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

Resources