Forum Discussion

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 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

Rufus_Test_Data_for_Combine.xlsx10 KB

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

12 Replies

HansVogelaar
MVP
Sep 28, 2021
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
- Rufus_Bertrand
  Brass Contributor
  Sep 29, 2021
  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
  Rufus_Test_Data_For_Combine_Results.xlsx11 KB
  - Rufus_Bertrand
    Brass Contributor
    Sep 29, 2021
    Don't know if this makes a difference, but there are 3 hidden columns after 'empno' column.
    
    thanks!