Sep 28 2021 08:54 AM
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
Sep 28 2021 11:18 AM
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
Sep 29 2021 06:11 AM - edited Sep 29 2021 06:27 AM
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
Sep 29 2021 06:28 AM
Sep 29 2021 07:02 AM - edited Sep 29 2021 07:03 AM
Hi Hans,
I'm uploading file showing the desired output. Sorry I should have started with that.
thanks
Rufus
Sep 29 2021 07:15 AM
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'"?
Sep 29 2021 07:20 AM
I see that I interpreted your original question differently. I'll take a look.
Sep 29 2021 07:29 AM
Sep 29 2021 07:30 AM
SolutionHere 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
Sep 29 2021 02:48 PM
Oct 01 2021 10:18 AM - edited Oct 01 2021 10:20 AM
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
Oct 01 2021 11:23 AM
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
Oct 02 2021 07:28 AM
Sep 29 2021 07:30 AM
SolutionHere 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