Convert multiple rows of data to columns?

[wolfgeek]

I have a worksheet of customer information where each customer may have one or more rows. The data in each column is the same except for the last column (NOTES) is different. Here is an example:

Columns:
CustNum
Last
First
Notes

Rows:
1 Jones Fred Nice Guy
1 Jones Fred Pays on time
2 Smith Greg Net 90
2 Smith Greg Late 4 times
2 Smith Greg Boss's Brother-in-law

I need to convert this to one row per customer with each note in their own column. So the final result would look like this:

1 Jones Fred Nice Guy Pays on time
2 Smith Greg Net 90 Late 4 times Boss's Brother-in-law

I THINK I can do this with something like =if(a2=a3,d3,"") in column e, then =if(a2=a4,d4,"") in column f, etc. But since there can be anywhere between 1 and 6 rows per customer, I'm getting a bit confused.

Can anyone assist me on this. I'm open to learning new/better ways of doing this.

Thank you!

 

[jbeaucaire]

This quick little macro will do it.

Option Explicit

Sub MergeRows()
Dim LR As Long, Rw As Long
Dim delRNG As Range

LR = Range("A" & Rows.Count).End(xlUp).Row      'last row with data
Set delRNG = Range("A" & LR + 1)                'seed the delrng

For Rw = LR To 3 Step -1        'from the bottom up, merge
    If Range("A" & Rw) = Range("A" & Rw - 1) Then
        Range(Range("D" & Rw), Cells(Rw, Columns.Count).End(xlToLeft)).Copy _
            Cells(Rw - 1, Columns.Count).End(xlToLeft).Offset(, 1)
        Set delRNG = Union(delRNG, Range("A" & Rw))
    End If
Next Rw

If delRNG.Rows.Count > 1 Then delRNG.EntireRow.Delete xlShiftUp
Columns.AutoFit
End Sub

How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save as a macro-enabled workbook

The macro is installed and ready to use. Press Alt-F8 and select MergeRows from the macro list.