Merge values in one column based on duplicates in another into a new worksheet

[nsheen]

Hi All,
I need to create a spreadsheet that I can use in a mail merge. I have a contact numbers in column A, and in column J I have the individual items that the contact purchased. I need to create a list of items for each unique contact number that I can then pull into an unordered list in a letter. How can I do that?
Thanks,
Nancy


Example original data
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Row[/TD]
[TD]Column A[/TD]
[TD]Column J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ContactID[/TD]
[TD]Purchase[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]VO-3476[/TD]
[TD]Black Shoes[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]VO-3476[/TD]
[TD]Black Socks[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]VO-3476[/TD]
[TD]White Sweater[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]VO-3482[/TD]
[TD]Red Dress[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]HS-3555[/TD]
[TD]Slippers (size 10)[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]BB-3996.BU[/TD]
[TD]Pink Glasses[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]BB-3996.BU[/TD]
[TD]Blue Glasses[/TD]
[/TR]
</tbody>[/TABLE]















Desired results on a new worksheet


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Row[/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ContactID[/TD]
[TD]Purchases[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]VO-3476[/TD]
[TD]Black Shoes
Black Socks
White Sweater[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]VO-3482[/TD]
[TD]Red Dress[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]HS-3555[/TD]
[TD]Slippers (size 10)[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]BB-3996.BU[/TD]
[TD]Pink Glasses
Blue Glasses[/TD]
[/TR]
</tbody>[/TABLE]









The letter would then pull in:
Dear Mary,
Thank you for purchasing

• Black Shoes
• Black Socks
• White Sweater

 

[hiker95]

nsheen,

Sample raw data in worksheet Sheet1 (already grouped/sorted by column A, ContactID):

Excel 2007
	A	J
1	ContactID	Purchase
2	VO-3476	Black Shoes
3	VO-3476	Black Socks
4	VO-3476	White Sweater
5	VO-3482	Red Dress
6	HS-3555	Slippers (size 10)
7	BB-3996.BU	Pink Glasses
8	BB-3996.BU	Blue Glasses
9
Sheet1

After the macro in a new worksheet Results:

Excel 2007
	A	B
1	ContactID	Purchases
2	VO-3476	Black Shoes Black Socks White Sweater
3	VO-3482	Red Dress
4	HS-3555	Slippers (size 10)
5	BB-3996.BU	Pink Glasses Blue Glasses
6
Results

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 06/20/2013
' http://www.mrexcel.com/forum/excel-questions/709711-merge-values-one-column-based-duplicates-another-into-new-worksheet.html
Dim w1 As Worksheet, wR As Worksheet
Dim r As Long, lr As Long, n As Long, nr As Long
Application.ScreenUpdating = False
Set w1 = Sheets("Sheet1")
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Sheets("Results")
wR.UsedRange.Clear
With wR.Cells(1, 1).Resize(, 2)
  .Value = Array("ContactID", "Purchases")
  .Font.Bold = True
End With
lr = w1.Cells(Rows.Count, 1).End(xlUp).Row
For r = 2 To lr
  n = Application.CountIf(w1.Columns(1), w1.Cells(r, 1).Value)
  nr = wR.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
  If n = 1 Then
    wR.Cells(nr, 1) = w1.Cells(r, 1)
    wR.Cells(nr, 2) = w1.Cells(r, 10)
  Else
    wR.Cells(nr, 1) = w1.Cells(r, 1)
    wR.Cells(nr, 2) = Join(Application.Transpose(w1.Range("J" & r & ":J" & (r + n) - 1)), Chr(10))
  End If
  r = r + n - 1
Next r
wR.Columns("A:B").AutoFit
wR.UsedRange.Rows.AutoFit
wR.Activate
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.