Merging duplicate fields

[felipelisboam]

Hello.

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

[TABLE="width: 631"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]FIRST_NAME[/TD]
[TD]LAST_NAME[/TD]
[TD]YEAR[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Jones[/TD]
[TD]2013[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Jones[/TD]
[TD]2012[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Jones[/TD]
[TD]2011[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Jones[/TD]
[TD]2009[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Jones[/TD]
[TD]2008[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 631"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]FIRST_NAME[/TD]
[TD]LAST_NAME[/TD]
[TD]YEAR[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Jones[/TD]
[TD]2008, 2009, 2011, 2012, 2013
[/TD]
[/TR]
</tbody>[/TABLE]


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

Thanks a lot!
Felipe

 

[bhos123]

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]

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

	A	B	C	D
First Name	Last Name	Year
John	Jones	a
John	Jones	b
John	Jones	c
John	Jones	d
John	Jones	e
First Name	Last Name	Year
John	Jones	2013 2012 2011 2009 2008	a b c d e

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2013[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2012[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2011[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]2009[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]2008[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C9[/TH]
[TD="align: left"]=CONCATENATE(2013&" ",2012&" ",2011&" ",2009&" ",2008&" ")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D9[/TH]
[TD="align: left"]=CONCATENATE("a ","b ","c ","d ","e ")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A9[/TH]
[TD="align: left"]{=INDEX($A$2:$A$6,MATCH(0,COUNTIF(A$8:A8,A$2:A$6),0))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B9[/TH]
[TD="align: left"]{=INDEX($B$2:$B$6,MATCH(0,COUNTIF(B$8:B8,B$2:B$6),0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]