Forum Discussion
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...
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
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 SubHi 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- Don't know if this makes a difference, but there are 3 hidden columns after 'empno' column.
thanks!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'"?
