Merge duplicate rows into one row

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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Welcome to the board...

That honestly sounds like a bad idea...very bad layout of data.

Most people would spend time finding ways to do the opposite.

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

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


From a record keeping point of view, the multiple rows of same name is preferable.
 
Upvote 0
Thanks Jonmo1,

I understand what you mean with bad layout of data so maybe I should explain. I am tidying up a master contact list so the rows have the name as the key identifier, but the columns have emails, phones, date of birth etc - some on the same row, some of the duplicate rows. I am not too worried about the layout of the columns as I can re-jig quickly... I was just stumped at merging the duplicate entries....I just wanted one row for one contact.


SudEx
 
Upvote 0
Sudex,

Try this (I use the example below):

1) Copy the code below.

Code:
Sub NamesData()
'
'Prg    : NamesData
'Author : Markmzz
'Date   : 24/05/2011
'
 
    'Explicitly defines the variables
    Dim LastRowL1, LastRowL2 As Long
    Dim i, j, k As Long
    Dim NameList2 As String
    Dim NNameList1, NColList2  As Long
 
    'Disable screen updating
    Application.ScreenUpdating = False
 
    'Determines the number of rows from the first list
    LastRowL1 = Cells(Rows.Count, 1).End(xlUp).Row
 
    'Sort in ascending order (columns A, B and C) the 1st list
    Range("A2:C" & LastRowL1).Sort _
        Key1:=Range("A2"), _
        Order1:=xlAscending
 
    'Create one sort list of the uniques names
    Range("A1").Copy
    Range("E1").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
 
    Range("A1:A" & LastRowL1).AdvancedFilter _
        Action:=xlFilterCopy, _
        CopyToRange:=Range("E1"), _
        Unique:=True
 
    'Determines the number of rows from the second list
    LastRowL2 = Cells(Rows.Count, 5).End(xlUp).Row
 
    'Navigate by Names of the 2nd lists
    For i = 2 To LastRowL2
        'Store the Name current 2nd list
        NameList2 = Cells(i, 5).Value
 
        'Position of the Name current in the 1st list
        k = Columns(1).Find(NameList2).Row
 
        'Determines the quantity of the current Name in the first list
        NNameList1 = WorksheetFunction.CountIf(Columns(1), "=" & NameList2)
 
        'Navigate by Names repeated of the 1st list
        For j = 1 To NNameList1
            'Updates the cell corresponding to the current Name of
            '2nd list with the data of Col2 and Col3 of 1st list
            Cells(i, (j - 1) * 2 + 6).Value = Cells(k - 1 + j, 2).Value
            Cells(i, (j - 1) * 2 + 7).Value = Cells(k - 1 + j, 3).Value
        Next j
    Next i
 
    'Determines the number of columns (Col2 e Col3) in 2nd List
    NColList2 = (Range("E1").CurrentRegion.Columns.Count - 1) / 2
 
    'Fill the labels of columns of 2nd List
    For j = 1 To NColList2
        Cells(1, (j - 1) * 2 + 6).Value = Cells(1, 2).Value
        Cells(1, (j - 1) * 2 + 7).Value = Cells(1, 3).Value
    Next j
 
    'Autofit the columns of 2nd Listl
    Range("E1").CurrentRegion.EntireColumn.AutoFit
 
    'Enable screen updating
    Application.ScreenUpdating = True
End Sub

2) Give a right-click in the Worksheet Tab that contains the list and choose View Code.

3) In VBA window that appears, choose Module on the Insert menu of the respective window.

4) Now, paste the code copied in step 1) and close the VBA window.

5) Again on the worksheet that contains the list, activate the View tab, choose Macros in the Macros group.

6) In the Macro dialog box, select the name of the macro NamesData and click the Run button.

<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH><TH>L</TH><TH>M</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="FONT-WEIGHT: bold">Col1</TD><TD style="FONT-WEIGHT: bold">Col2</TD><TD style="FONT-WEIGHT: bold">Col3</TD><TD style="TEXT-ALIGN: right"></TD><TD>Col1</TD><TD>Col2</TD><TD>Col3</TD><TD>Col2</TD><TD>Col3</TD><TD>Col2</TD><TD>Col3</TD><TD>Col2</TD><TD>Col3</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="BACKGROUND-COLOR: #ffff00">Name01</TD><TD style="BACKGROUND-COLOR: #ffff00">01234 123 456</TD><TD style="BACKGROUND-COLOR: #ffff00">ABCDE01</TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ffff00">Name01</TD><TD>01234 123 456</TD><TD>ABCDE01</TD><TD>98765 544 454</TD><TD>ABCDE02</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="BACKGROUND-COLOR: #ffff00">Name01</TD><TD style="BACKGROUND-COLOR: #ffff00">98765 544 454</TD><TD style="BACKGROUND-COLOR: #ffff00">ABCDE02</TD><TD style="TEXT-ALIGN: right"></TD><TD>Name02</TD><TD>1235 123 456</TD><TD>ABCDE03</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD>Name02</TD><TD>1235 123 456</TD><TD>ABCDE03</TD><TD style="TEXT-ALIGN: right"></TD><TD>Name03</TD><TD>98765 544 455</TD><TD>ABCDE04</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD>Name03</TD><TD>98765 544 455</TD><TD>ABCDE04</TD><TD style="TEXT-ALIGN: right"></TD><TD>Name04</TD><TD>1236 123 456</TD><TD>ABCDE05</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>Name04</TD><TD>1236 123 456</TD><TD>ABCDE05</TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ffff00">Name05</TD><TD>98765 544 456</TD><TD>ABCDE06</TD><TD>1237 123 456</TD><TD>ABCDE07</TD><TD>98765 544 457</TD><TD>ABCDE08</TD><TD>1238 123 456</TD><TD>ABCDE09</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="BACKGROUND-COLOR: #ffff00">Name05</TD><TD style="BACKGROUND-COLOR: #ffff00">98765 544 456</TD><TD style="BACKGROUND-COLOR: #ffff00">ABCDE06</TD><TD style="TEXT-ALIGN: right"></TD><TD>Name07</TD><TD>98765 544 458</TD><TD>ABCDE10</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="BACKGROUND-COLOR: #ffff00">Name05</TD><TD style="BACKGROUND-COLOR: #ffff00">1237 123 456</TD><TD style="BACKGROUND-COLOR: #ffff00">ABCDE07</TD><TD style="TEXT-ALIGN: right"></TD><TD>Name08</TD><TD>1239 123 456</TD><TD>ABCDE11</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="BACKGROUND-COLOR: #ffff00">Name05</TD><TD style="BACKGROUND-COLOR: #ffff00">98765 544 457</TD><TD style="BACKGROUND-COLOR: #ffff00">ABCDE08</TD><TD style="TEXT-ALIGN: right"></TD><TD>Name09</TD><TD>98765 544 459</TD><TD>ABCDE12</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="BACKGROUND-COLOR: #ffff00">Name05</TD><TD style="BACKGROUND-COLOR: #ffff00">1238 123 456</TD><TD style="BACKGROUND-COLOR: #ffff00">ABCDE09</TD><TD style="TEXT-ALIGN: right"></TD><TD>Name10</TD><TD>1240 123 456</TD><TD>ABCDE13</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD>Name07</TD><TD>98765 544 458</TD><TD>ABCDE10</TD><TD style="TEXT-ALIGN: right"></TD><TD>Name11</TD><TD>98765 544 460</TD><TD>ABCDE14</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD>Name08</TD><TD>1239 123 456</TD><TD>ABCDE11</TD><TD style="TEXT-ALIGN: right"></TD><TD>Name12</TD><TD>1241 123 456</TD><TD>ABCDE15</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD>Name09</TD><TD>98765 544 459</TD><TD>ABCDE12</TD><TD style="TEXT-ALIGN: right"></TD><TD>Name13</TD><TD>98765 544 461</TD><TD>ABCDE16</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD>Name10</TD><TD>1240 123 456</TD><TD>ABCDE13</TD><TD style="TEXT-ALIGN: right"></TD><TD>Name14</TD><TD>1242 123 456</TD><TD>ABCDE17</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD>Name11</TD><TD>98765 544 460</TD><TD>ABCDE14</TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #ffff00">Name15</TD><TD>98765 544 462</TD><TD>ABCDE18</TD><TD>1243 123 456</TD><TD>ABCDE19</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD>Name12</TD><TD>1241 123 456</TD><TD>ABCDE15</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD>Name13</TD><TD>98765 544 461</TD><TD>ABCDE16</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD>Name14</TD><TD>1242 123 456</TD><TD>ABCDE17</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD style="BACKGROUND-COLOR: #ffff00">Name15</TD><TD style="BACKGROUND-COLOR: #ffff00">98765 544 462</TD><TD style="BACKGROUND-COLOR: #ffff00">ABCDE18</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD style="BACKGROUND-COLOR: #ffff00">Name15</TD><TD style="BACKGROUND-COLOR: #ffff00">1243 123 456</TD><TD style="BACKGROUND-COLOR: #ffff00">ABCDE19</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">21</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">22</TD><TD>********</TD><TD>************</TD><TD>*********</TD><TD>********</TD><TD>********</TD><TD>************</TD><TD>*********</TD><TD>************</TD><TD>*********</TD><TD>************</TD><TD>*********</TD><TD>************</TD><TD>*********</TD></TR></TBODY></TABLE>


Is this what you want?

Markmzz
 
Upvote 0
Sudex,

I create a new version (v2) of the macro, try this:

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 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("A2"), _
        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
            'Add one to the counter of the current Row in the 1st list
            RL1 = RL1 + 1
        Loop
    Next RL2
 
    'Determines the number of columns (Col2, Col3,...) in 2nd List
    NCL2 = (Cells(1, NextCol).CurrentRegion.Columns.Count - 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


<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH><TH>K</TH><TH>L</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Col1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Col2</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Col3</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Col4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">Col1</TD><TD>Col2</TD><TD>Col3</TD><TD>Col4</TD><TD>Col2</TD><TD>Col3</TD><TD>Col4</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center">Name06</TD><TD style="TEXT-ALIGN: center">41867 551</TD><TD style="TEXT-ALIGN: center">RRHZXY</TD><TD style="TEXT-ALIGN: center">95281 574</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">Name01</TD><TD>65861 875</TD><TD>YESNVF</TD><TD>80893 595</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center">Name24</TD><TD style="TEXT-ALIGN: center">74950 146</TD><TD style="TEXT-ALIGN: center">HEVSAC</TD><TD style="TEXT-ALIGN: center">82288 798</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">Name02</TD><TD>06927 306</TD><TD>GJBSKH</TD><TD>83772 642</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">Name28</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">14406 176</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">RFIJKC</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">95329 220</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">Name03</TD><TD>99248 843</TD><TD>PWFVSP</TD><TD>04141 605</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: center">Name04</TD><TD style="TEXT-ALIGN: center">31765 834</TD><TD style="TEXT-ALIGN: center">XBOXWZ</TD><TD style="TEXT-ALIGN: center">03061 983</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">Name04</TD><TD>31765 834</TD><TD>XBOXWZ</TD><TD>03061 983</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">Name25</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">20487 905</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">JOQVXM</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">05484 880</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">Name06</TD><TD>41867 551</TD><TD>RRHZXY</TD><TD>95281 574</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: center">Name37</TD><TD style="TEXT-ALIGN: center">53969 618</TD><TD style="TEXT-ALIGN: center">SYGOFO</TD><TD style="TEXT-ALIGN: center">01484 310</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">Name08</TD><TD>78422 460</TD><TD>LEJNRH</TD><TD>49839 151</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: center">Name03</TD><TD style="TEXT-ALIGN: center">99248 843</TD><TD style="TEXT-ALIGN: center">PWFVSP</TD><TD style="TEXT-ALIGN: center">04141 605</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">Name09</TD><TD>10285 962</TD><TD>LGZFYM</TD><TD>52659 210</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: center">Name22</TD><TD style="TEXT-ALIGN: center">24111 605</TD><TD style="TEXT-ALIGN: center">BGUKFW</TD><TD style="TEXT-ALIGN: center">32725 732</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">Name10</TD><TD>08001 543</TD><TD>RRGEOJ</TD><TD>88874 481</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">Name28</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">68088 742</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">LXLWOG</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">94367 419</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">Name14</TD><TD>07168 932</TD><TD>FDLVFR</TD><TD>94713 181</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: center">Name29</TD><TD style="TEXT-ALIGN: center">84854 618</TD><TD style="TEXT-ALIGN: center">VJKZKB</TD><TD style="TEXT-ALIGN: center">95602 643</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">Name22</TD><TD>24111 605</TD><TD>BGUKFW</TD><TD>32725 732</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">12</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">Name36</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">28607 159</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">FYMKDJ</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">88209 390</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">Name24</TD><TD>74950 146</TD><TD>HEVSAC</TD><TD>82288 798</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">13</TD><TD style="TEXT-ALIGN: center">Name02</TD><TD style="TEXT-ALIGN: center">06927 306</TD><TD style="TEXT-ALIGN: center">GJBSKH</TD><TD style="TEXT-ALIGN: center">83772 642</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">Name25</TD><TD>20487 905</TD><TD>JOQVXM</TD><TD>05484 880</TD><TD>91524 798</TD><TD>DKUOAT</TD><TD>05570 461</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">14</TD><TD style="TEXT-ALIGN: center">Name10</TD><TD style="TEXT-ALIGN: center">08001 543</TD><TD style="TEXT-ALIGN: center">RRGEOJ</TD><TD style="TEXT-ALIGN: center">88874 481</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">Name28</TD><TD>14406 176</TD><TD>RFIJKC</TD><TD>95329 220</TD><TD>68088 742</TD><TD>LXLWOG</TD><TD>94367 419</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">15</TD><TD style="TEXT-ALIGN: center">Name08</TD><TD style="TEXT-ALIGN: center">78422 460</TD><TD style="TEXT-ALIGN: center">LEJNRH</TD><TD style="TEXT-ALIGN: center">49839 151</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">Name29</TD><TD>84854 618</TD><TD>VJKZKB</TD><TD>95602 643</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">16</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">Name25</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">91524 798</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">DKUOAT</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">05570 461</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">Name36</TD><TD>28607 159</TD><TD>FYMKDJ</TD><TD>88209 390</TD><TD>72344 253</TD><TD>DJWTNM</TD><TD>64471 839</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">17</TD><TD style="TEXT-ALIGN: center">Name01</TD><TD style="TEXT-ALIGN: center">65861 875</TD><TD style="TEXT-ALIGN: center">YESNVF</TD><TD style="TEXT-ALIGN: center">80893 595</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center">Name37</TD><TD>53969 618</TD><TD>SYGOFO</TD><TD>01484 310</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">18</TD><TD style="TEXT-ALIGN: center">Name14</TD><TD style="TEXT-ALIGN: center">07168 932</TD><TD style="TEXT-ALIGN: center">FDLVFR</TD><TD style="TEXT-ALIGN: center">94713 181</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">19</TD><TD style="TEXT-ALIGN: center">Name09</TD><TD style="TEXT-ALIGN: center">10285 962</TD><TD style="TEXT-ALIGN: center">LGZFYM</TD><TD style="TEXT-ALIGN: center">52659 210</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">20</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">Name36</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">72344 253</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">DJWTNM</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #ffff00">64471 839</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">21</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">22</TD><TD style="TEXT-ALIGN: center">List</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD>Result</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">23</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">24</TD><TD>**********</TD><TD>**********</TD><TD>**********</TD><TD>**********</TD><TD>**********</TD><TD>**********</TD><TD>**********</TD><TD>**********</TD><TD>**********</TD><TD>**********</TD><TD>**********</TD><TD>**********</TD></TR></TBODY></TABLE>



Markmzz
 
Upvote 0
Markmzz,

Oh my god!!! You are truly an Excel Ninja! That has helped me so much and saved a lot of time... ! Thank you - now I just need to understand how you created the code... I am quite good in Excel, but am starting to learn VBA...

Thanks for all your help!

SudEx
 
Upvote 0
Markmzz,

Oh my god!!! You are truly an Excel Ninja! That has helped me so much and saved a lot of time... ! Thank you - now I just need to understand how you created the code... I am quite good in Excel, but am starting to learn VBA...

Thanks for all your help!

SudEx

Sudex,

Thanks for the feedback and for start to learn VBA try the book Excel 2007 Visual Basic for Applications - Step by Step.

Markmzz
 
Upvote 0
Hi Markmzz,

Quick one....the macro you sent... it works perfectly for the first 315 uniques or so records, but for the rest of the records (theres 855 uniques in total), it doesnt copy across any data in the columns. It filters the names, but the columns are blank from rec # 315 - any ideas? I suspect its just a small tweaking of VBA - I tried to have a look but then got a bit lost !

Any help would be very much appreciated!
 
Upvote 0
Hi Markmzz,

Quick one....the macro you sent... it works perfectly for the first 315 uniques or so records, but for the rest of the records (theres 855 uniques in total), it doesnt copy across any data in the columns. It filters the names, but the columns are blank from rec # 315 - any ideas? I suspect its just a small tweaking of VBA - I tried to have a look but then got a bit lost !

Any help would be very much appreciated!
Sudex,

What's different after the line 315?

Could you post some data after the line 313?

Markmzz
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,238
Members
448,555
Latest member
RobertJones1986

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