Merge duplicate rows into one row

S

Sudex

New Member
Joined
May 24, 2011
Messages
8
Hi Guys,

Have just signed up recently. Already have my first question. I am working with a sheet that has rows of Names, with many duplicates. However, the column data for the duplicates are different. I want to merge them all in one row. So, for example, I have

Joe Smith | 01234 123 456 | ABCDE
Joe Smith | 98765 544 454 | ASDFG

Need:

Joe Smith | 01234 123 456 | ABCDE | 98765 544 454 | ASDFG

or something similiar. The order of the final columns doesn't matter.

Thanks all !

SudEx
 
Sort by date Sort by votes
markmzz said:
Sudex,

What's different after the line 315?

Could you post some data after the line 313?

Markmzz
Click to expand...

Markmzz,

My raw data had 1300 rows, when I use your macro, it creates the second list with 855 rows (which is correct). Out of these, the first 315 rows have data in the columns, but after that, the other columns that are populated is the name column, the rest is blank. So for example:

Name Phone Number Country
------------------------------------------
John Smith 01262 752 755 UK
Joe Bosch 87867 232 232 China
Robert Jones (blank) (blank) <--- row 315 onwards
Steve Martin (blank) (blank)

Your macro inserts the duplicates in columns titled Misc1, 2 etc which is perfect, but after row 315, it just puts the name and none of the columns are populated. }

Does that help?

Thanks,

Sudex
 
Upvote 0

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sudex said:
Markmzz,

My raw data had 1300 rows, when I use your macro, it creates the second list with 855 rows (which is correct). Out of these, the first 315 rows have data in the columns, but after that, the other columns that are populated is the name column, the rest is blank. So for example:

Name Phone Number Country
------------------------------------------
John Smith 01262 752 755 UK
Joe Bosch 87867 232 232 China
Robert Jones (blank) (blank) <--- row 315 onwards
Steve Martin (blank) (blank)

Your macro inserts the duplicates in columns titled Misc1, 2 etc which is perfect, but after row 315, it just puts the name and none of the columns are populated. }

Does that help?

Thanks,

Sudex
Click to expand...

Sudex,

I need the data of List 01 between Joe Bosch and Robert Jones to try to figure out what is the difference that is causing the problem.

Markmzz
 
Upvote 0
Hi MArkmzz,

I can send you my file by changing the content slighlty - but erm, how do I do that on here? Theres no 'attach' button... ?
 
Upvote 0
Sudex said:
Hi MArkmzz,

I can send you my file by changing the content slighlty - but erm, how do I do that on here? Theres no 'attach' button... ?
Click to expand...

Sudex,

I send to you one private message. Check your private messages box.

Markmzz
 
Upvote 0
Sudex,

I believe that I resolved the problem.

What happened was that the count of columns from the second list was being made incorrectly. I was using the current region, but its data has several empty cells and therefore was causing the error.

Test the code below calmly and give us a feedback.

Obs: I send to you a email.

Code: 
Sub NamesData_v2()
'
'Prg    : NamesData_v2
'Author : Markmzz
'Date   : 25/05/2011
'Version: 02
'
    'Explicitly defines the variables
    Dim LastRowL1, LastRowL2, LastColL1, NextCol As Long
    Dim RL1, RL2, CL1, CL2, NCL1, NCL2, CCL2, LCL2 As Long
    Dim NameList2 As String
 
    'Disable screen updating
    Application.ScreenUpdating = False
 
    'Determines the number of rows of the first list
    LastRowL1 = Cells(Rows.Count, 1).End(xlUp).Row
 
    'Determines the number of columns of the first list
    LastColL1 = Cells(1, Columns.Count).End(xlToLeft).Column
 
    'Determines the number of columns with
    'Name column out of the first list
    NCL1 = LastColL1 - 1
 
    'Sort, in ascending order, the 1st list
    Range(Cells(2, 1), Cells(LastRowL1, LastColL1)).Sort _
        Key1:=Range("A1"), _
        Order1:=xlAscending
 
    'Initial Column of the 2nd list
    NextCol = LastColL1 + 2
 
    'Create one sort list of unique names (list 2)
    Range(Cells(1, 1), Cells(LastRowL1, 1)).AdvancedFilter _
        Action:=xlFilterCopy, _
        CopyToRange:=Range(Cells(1, NextCol).Address), _
        Unique:=True
    Cells(1, NextCol).Font.Bold = False
 
    'Determines the number of rows from the second list
    LastRowL2 = Cells(Rows.Count, NextCol).End(xlUp).Row
 
    'Define two with the current Row in the 1st list
    RL1 = 2
 
    'Navigate by Names of the 2nd list
    For RL2 = 2 To LastRowL2
        'Show the progress in the Status Bar of the Excel
        Application.StatusBar = "Processing row " & RL2 & " of " & LastRowL2
        'Store the current Name of the 2nd list
        NameList2 = Cells(RL2, NextCol).Value
 
        'Define NextCol+1 with the current Column in the 2nd list
        CL2 = NextCol + 1
 
        'Navigate by Names in the 1st list that are equal
        'the current Name in the 2nd lists
        Do While Cells(RL1, 1) = NameList2
            'Fill, in the 2nd list, the data of the current name
            For CL1 = 2 To LastColL1
                Cells(RL2, CL2).Value = Cells(RL1, CL1).Value
                'Add one to the counter of the current Column in the 2st list
                CL2 = CL2 + 1
            Next CL1
 
            'New *********************************************************** New
            'New *********************************************************** New
            'Determines the last column of the 2nd list
            If LCL2 < CL2 Then
                LCL2 = CL2
            End If
            'Add one to the counter of the current Row in the 1st list
            RL1 = RL1 + 1
        Loop
    Next RL2
 
    'New ***************************************************************** New
    'New ***************************************************************** New
    'Determines the number of columns in group (Col2, Col3,...) in 2nd List
    NCL2 = (LCL2 - NextCol - 1) / NCL1
 
    'Fill the labels of columns of 2nd List
    For CCL2 = 1 To NCL2
        For CL1 = 2 To LastColL1
            Cells(1, (CCL2 - 1) * NCL1 + NextCol + CL1 - 1).Value = _
                Cells(1, CL1).Value
        Next CL1
    Next CCL2
 
    'Autofit the columns of 2nd List
    Cells(1, NextCol).CurrentRegion.EntireColumn.AutoFit
 
    'Enable screen updating
    Application.ScreenUpdating = True
 
    'Reset the Status Bar of the Excel
    Application.StatusBar = False
End Sub
Markmzz
 
Upvote 0
markmzz said:
Sudex,

I believe that I resolved the problem.

What happened was that the count of columns from the second list was being made incorrectly. I was using the current region, but its data has several empty cells and therefore was causing the error.

Test the code below calmly and give us a feedback.

Obs: I send to you a email.

Code: 
Sub NamesData_v2()
'
'Prg    : NamesData_v2
'Author : Markmzz
'Date   : 25/05/2011
'Version: 02
'
    'Explicitly defines the variables
    Dim LastRowL1, LastRowL2, LastColL1, NextCol As Long
    Dim RL1, RL2, CL1, CL2, NCL1, NCL2, CCL2, LCL2 As Long
    Dim NameList2 As String
 
    'Disable screen updating
    Application.ScreenUpdating = False
 
    'Determines the number of rows of the first list
    LastRowL1 = Cells(Rows.Count, 1).End(xlUp).Row
 
    'Determines the number of columns of the first list
    LastColL1 = Cells(1, Columns.Count).End(xlToLeft).Column
 
    'Determines the number of columns with
    'Name column out of the first list
    NCL1 = LastColL1 - 1
 
    'Sort, in ascending order, the 1st list
    Range(Cells(2, 1), Cells(LastRowL1, LastColL1)).Sort _
        Key1:=Range("A1"), _
        Order1:=xlAscending
 
    'Initial Column of the 2nd list
    NextCol = LastColL1 + 2
 
    'Create one sort list of unique names (list 2)
    Range(Cells(1, 1), Cells(LastRowL1, 1)).AdvancedFilter _
        Action:=xlFilterCopy, _
        CopyToRange:=Range(Cells(1, NextCol).Address), _
        Unique:=True
    Cells(1, NextCol).Font.Bold = False
 
    'Determines the number of rows from the second list
    LastRowL2 = Cells(Rows.Count, NextCol).End(xlUp).Row
 
    'Define two with the current Row in the 1st list
    RL1 = 2
 
    'Navigate by Names of the 2nd list
    For RL2 = 2 To LastRowL2
        'Show the progress in the Status Bar of the Excel
        Application.StatusBar = "Processing row " & RL2 & " of " & LastRowL2
        'Store the current Name of the 2nd list
        NameList2 = Cells(RL2, NextCol).Value
 
        'Define NextCol+1 with the current Column in the 2nd list
        CL2 = NextCol + 1
 
        'Navigate by Names in the 1st list that are equal
        'the current Name in the 2nd lists
        Do While Cells(RL1, 1) = NameList2
            'Fill, in the 2nd list, the data of the current name
            For CL1 = 2 To LastColL1
                Cells(RL2, CL2).Value = Cells(RL1, CL1).Value
                'Add one to the counter of the current Column in the 2st list
                CL2 = CL2 + 1
            Next CL1
 
            'New *********************************************************** New
            'New *********************************************************** New
            'Determines the last column of the 2nd list
            If LCL2 < CL2 Then
                LCL2 = CL2
            End If
            'Add one to the counter of the current Row in the 1st list
            RL1 = RL1 + 1
        Loop
    Next RL2
 
    'New ***************************************************************** New
    'New ***************************************************************** New
    'Determines the number of columns in group (Col2, Col3,...) in 2nd List
    NCL2 = (LCL2 - NextCol - 1) / NCL1
 
    'Fill the labels of columns of 2nd List
    For CCL2 = 1 To NCL2
        For CL1 = 2 To LastColL1
            Cells(1, (CCL2 - 1) * NCL1 + NextCol + CL1 - 1).Value = _
                Cells(1, CL1).Value
        Next CL1
    Next CCL2
 
    'Autofit the columns of 2nd List
    Cells(1, NextCol).CurrentRegion.EntireColumn.AutoFit
 
    'Enable screen updating
    Application.ScreenUpdating = True
 
    'Reset the Status Bar of the Excel
    Application.StatusBar = False
End Sub
Markmzz
Click to expand...

Hi Markmzz,

Thanks very much for the file and the new code. I will test it thoroughly and get back to you tomorrow - Need to ensure it works for all records and also try and understand the code myself...

Thanks again for your help.

SudEx
 
Upvote 0
It's there a way to have this macro copy the results to another sheet
I'll like to have the material type and the quantities for each material sum up to another sheet this way

Is it possible to have this accomplish in excel?
Any Help will be Greatly appreciated.

Sheet1.jpg
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Combine rows based on previous column duplicate data
Replies
2
Views
203
jessiehayden
J
J
  • Solved
VBA to merge duplicate rows and sum values in certain column with adjustments
Replies
9
Views
330
Alex Blakenburg
A
M
  • Question
Insert merged row into Excel, with duplication of the row(s) formatting above, using Command Button
Replies
1
Views
390
sxhall
S
FatalLordes
  • Question
Looking for duplicates and merging data
Replies
3
Views
1K
FatalLordes
FatalLordes
S
  • Solved
Help, merging data rows with unique values.
Replies
6
Views
404
alansidman
A

Forum statistics

Threads
1,215,004
Messages
6,122,659
Members
449,091
Latest member
peppernaut

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top