Merging duplicate fields

felipelisboam

New Member
Joined
Jul 18, 2016
Messages
1
Hello.

Im having trouble merging duplicate fields with same First and Last name. My spreadsheet looks like this:

FIRST_NAMELAST_NAMEYEAR
JohnJones2013
JohnJones2012
JohnJones2011
JohnJones2009
JohnJones2008

<colgroup><col><col><col></colgroup><tbody>
</tbody>

I basically need to combine it all under one row to look like this:

FIRST_NAMELAST_NAMEYEAR
JohnJones2008, 2009, 2011, 2012, 2013

<colgroup><col><col><col></colgroup><tbody>
</tbody>


Can someone please help? Been struggling with this for a while.

Thanks a lot!
Felipe
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

bhos123

Well-known Member
Joined
May 2, 2016
Messages
876
Sub macro1()
Dim lngLastRow As String
Dim lastRow As Long
Dim lastcolumn As Long
Application.ScreenUpdating = False

lastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
lastcolumn = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count

ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range(Cells(2, 1), Cells(lastRow, 1)), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=Range(Cells(2, 2), Cells(lastRow, 2)), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveSheet.Sort
.SetRange Range(Cells(1, 1), Cells(lastRow, lastcolumn))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

For i = lastRow To 2 Step -1

If Cells(i, 1).Value = Cells(i - 1, 1).Value And Cells(i, 2).Value = Cells(i - 1, 2).Value Then
Cells(i - 1, 3).Value = Cells(i - 1, 3).Value & " , " & Cells(i, 3).Value
Rows(i).EntireRow.Delete
End If

Next
Application.ScreenUpdating = True

End Sub
 

Mike Szczesny

Active Member
Joined
Feb 7, 2008
Messages
411
Can you use something like this? The first two formulas selects for unique items. The formula for years for years curiously requires the &" " to allow for spaces. However, I did the same for a text sample. if you enter for example enter a (plus space), then enter. Then the same for b to do. This same formula will show spaces between letters. For the year formula, you need to enter the formula. Then use your mouse to highlight the transpose part of the formula. Select F9.This will show {"a","b"......}. You need to remove both {'s. Then press enter. The original formula is =concatenate(transpose(range)). Use F9 select to transpose part .it looks like the transpose part of the formula disappears, leaving only =concatenate

Excel 2012
ABCD
1First NameLast NameYear
2JohnJones2013a
3JohnJones2012b
4JohnJones2011c
5JohnJones2009d
6JohnJones2008e
7
8First NameLast NameYear
9JohnJones2013 2012 2011 2009 2008 a b c d e

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
C9=CONCATENATE(2013&" ",2012&" ",2011&" ",2009&" ",2008&" ")
D9=CONCATENATE("a ","b ","c ","d ","e ")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
A9{=INDEX($A$2:$A$6,MATCH(0,COUNTIF(A$8:A8,A$2:A$6),0))}
B9{=INDEX($B$2:$B$6,MATCH(0,COUNTIF(B$8:B8,B$2:B$6),0))}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,108,596
Messages
5,523,804
Members
409,535
Latest member
chiefskingdom98

This Week's Hot Topics

Top