SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2792203%22%20slang%3D%22en-US%22%3EI%20need%20to%20combine%20rows%20in%20spreadsheet%20that%20have%20common%20employee%20number.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2792203%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20using%20Microsoft%20Excel%20for%20Microsoft%20365%20MSO%20version%2016.0.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20of%20employee%20information%2C%20I%20have%203%20rows%20per%20employee%20and%20need%20to%20combine%20those%20rows%20into%201%20row%20adding%202%20columns%20from%20the%202nd%20and%203rd%20rows%20to%20the%201st%20row%20where%20column%20'empno'%20matches%20in%20other%20rows.%3C%2FP%3E%3CP%3EThe%20columns%20I%20want%20to%20include%20on%201st%20row%20are%20columns%20'M'%20and%20'N'.%3C%2FP%3E%3CP%3EI'm%20attaching%20file%20of%20sample%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20greatly%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethanks%2C%3C%2FP%3E%3CP%3ERufus%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2792203%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2792794%22%20slang%3D%22en-US%22%3ERe%3A%20I%20need%20to%20combine%20rows%20in%20spreadsheet%20that%20have%20common%20employee%20number.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2792794%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1086093%22%20target%3D%22_blank%22%3E%40Rufus_Bertrand%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20a%20macro%20you%20can%20run.%20Please%20test%20on%20a%20copy%20of%20your%20worksheet%20first!%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20CombineRows()%0A%20%20%20%20Dim%20r%20As%20Long%0A%20%20%20%20Dim%20m%20As%20Long%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20m%20%3D%20Cells(Rows.Count%2C%205).End(xlUp).Row%0A%20%20%20%20For%20r%20%3D%202%20To%20m%20Step%203%0A%20%20%20%20%20%20%20%20Cells(r%2C%2015).Resize(1%2C%202).Value%20%3D%20Cells(r%20%2B%201%2C%2013).Resize(1%2C%202).Value%0A%20%20%20%20%20%20%20%20Cells(r%2C%2017).Resize(1%2C%202).Value%20%3D%20Cells(r%20%2B%202%2C%2013).Resize(1%2C%202).Value%0A%20%20%20%20Next%20r%0A%20%20%20%20Range(%22A1%3AR%22%20%26amp%3B%20m).RemoveDuplicates%20Columns%3A%3D5%2C%20Header%3A%3DxlYes%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2795176%22%20slang%3D%22en-US%22%3ERe%3A%20I%20need%20to%20combine%20rows%20in%20spreadsheet%20that%20have%20common%20employee%20number.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2795176%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20again%20Hans!%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20for%20the%20macro!%3CBR%20%2F%3EI%20copied%20it%20into%20the%20test%20data%20I%20sent%20you%20and%20I'm%20uploading%20the%20results.%3CBR%20%2F%3EIt%20looks%20like%20it's%20not%20getting%20the%20correct%20values%20from%20columns%20L-N%3F%3CBR%20%2F%3EI%20copied%20macro%20as%20delivered%20and%20didn't%20change%20anything.%3CBR%20%2F%3E%3CBR%20%2F%3EAlso%2C%20I%20searched%20for%20the%20'Step%203'%20command%20in%20the%20'For%20r%20%3D%202%20To%20Step%203'%20line%20in%20Google%20and%20didn't%20find%20any%20information%20on%20it.%20Can%20you%20explain%20what%20this%20is%20doing%3F%3CBR%20%2F%3E%3CBR%20%2F%3Ethanks%3CBR%20%2F%3ERufus%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2795229%22%20slang%3D%22en-US%22%3ERe%3A%20I%20need%20to%20combine%20rows%20in%20spreadsheet%20that%20have%20common%20employee%20number.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2795229%22%20slang%3D%22en-US%22%3EDon't%20know%20if%20this%20makes%20a%20difference%2C%20but%20there%20are%203%20hidden%20columns%20after%20'empno'%20column.%3CBR%20%2F%3E%3CBR%20%2F%3Ethanks!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2795337%22%20slang%3D%22en-US%22%3ERe%3A%20I%20need%20to%20combine%20rows%20in%20spreadsheet%20that%20have%20common%20employee%20number.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2795337%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Hans%2C%3CBR%20%2F%3EI'm%20uploading%20file%20showing%20the%20desired%20output.%20Sorry%20I%20should%20have%20started%20with%20that.%3CBR%20%2F%3E%3CBR%20%2F%3Ethanks%3CBR%20%2F%3ERufus%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

12 Replies

@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

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

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

thanks!

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

thanks
Rufus

@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'"?

@Rufus_Bertrand 

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

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!
best response confirmed by Rufus_Bertrand (Occasional Contributor)
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
thanks Hans, this code works great!

Thanks for saving the day again:)

Rufus

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

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