Excel 2010 - Merge data in one column based on another column?

Question

My data looks like this:
UniqueID Date Name
1 4/12/2012 Jane
1 5/20/2012 Jane
1 7/12/2012 Jane
2 1/1/2012 Jim
2 5/4/2012 Jim

Note that I have separte rows for each date. I want just one row for each UniqueID and needs the dates to be comma separated. Here is how my data should look like:

UniqueID Date Name
1 4/12/2012, 5/20/2012, 7/12/2012 Jane
2 1/1/2012, 5/4/2012 Jim


I have thousands of records to work with and would appreciate help from any Excel Guru out there :)

Answer 1

Here is a method to do as you wish simply by double clicking any cell. This assumes that your data to evaluate is in columns A:C, and you wish to have one line entry for each ID, with column B to contain all data entries, separated by commas. This is a 'run once' event handler. It will only trigger on the first double click.



Copy the following event handler to the clipboard (highlight the entire code, right click inside the highlighted area, and 'Copy'):





Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _

Cancel As Boolean)

Dim i, LastRow, tempVar

LastRow = Application.Cells. SpecialCells(xlCellTypeLastCell).Row

Application.ScreenUpdating = False

If Cells(1, "IV").Value = "X" Then

Target.Offset(0, 1).Select

Exit Sub

End If

Range("IU:IW").ClearContents

For i = 1 To LastRow

If Cells(i, "A").Value = Cells(i + 1, "A").Value Then

tempVar = tempVar & Cells(i, "B").Value & ", "

GoTo here

Else

tempVar = tempVar & Cells(i, "B").Value

Range("IU" & Rows.Count).End(xlUp).Offset(1, 0).Value = Cells(i, "A").Value

Range("IV" & Rows.Count).End(xlUp).Offset(1, 0).Value = tempVar

Range("IW" & Rows.Count).End(xlUp).Offset(1, 0).Value = Cells(i, "C").Value

tempVar = ""

End If

here:

Next

Range("A:C").ClearContents

Columns("IU:IW").Select

Selection.Cut Destination:=Columns("A:C")

Columns("B:B").AutoFit

Target.Offset(0, 1).Select

Range("IV1").Value = "X"

Range("IV1").EntireColumn.Hidden = True

End Sub



Select the worksheet containing the data to evaluate and right click the sheet tab at the bottom.



Select 'View Code'.



Paste the event handler into the white editing area to the right (right click inside the area and 'Paste').



Close the VBE (red button w/white 'x' - top right).



Double click any cell to re-sequence the data.



Note: this has been tested a number of times and returns the appropriate result. However, it is always wise to back up one's work to prevent the accidental smoking crater...

Answer 2

There are two ways of doing this with excel. One is writing a VBA program the other is using the spreadsheet features.



The algorithm for this merge is quite simple. Scan the list joining the date strings together. When the id changes write out the record and load the next one. Repeat untill done.



We could do a variation of this in the spreadsheet.

Take a copy of the data because the process mangles the list.

Add a column that tests for the change in ID. On change set the cell to the date of the current line on no change concatinate the cell above with the date on the current line.

Add a second column.

It should test downward for change in ID. On change set the cell to a "X". It's just a mark to know which record to select.

Copy the new columns and paste special to values. This is to freeze the data we pulled out so the sort won't break it.

Sort the records on the column with the frozen marker then the ID. This should give you the data close to the form you want. A little more excel work should do it.

Answer 3

Unless done programatically (VBA code behind in a Macro), merging would not directly provide a solution for you. You can however solve this with a mix of several Excel features/functions. A few steps and it is done:

1. GATHER DATA:

- Sort (if you need/have to) all data according to the UniqueID column

- Create an additional column to write the following formula

- In the first data row, write a formula to concatenate Date of current row with Date of previous row IF UniqueID of current row is the same UniqueID of previous row.

- Copy formula to below rows.

- Create a new column to write a formula to flag the ultimate row to be used.

- In new column write formula that sets the value as 1 when UniqueID of current row is different from UniqueID from following row.



2. HIDE UNNEEDED DATA

- Hide the original Date column and rename your Date column with the date formulas as "Date"

- Set up a data filter on your header row. Filter on the flag column: list records with value of 1 only.

- Hide your flagging column



3. COPY DATA

- Copy the filtered data set to a new workbook or spreadsheet if you need to present to someone else.



* Let me know if you need help with the formulas. There will only be 2 set. They'll be quite easy.