Excel 2007 : Combine data from multiple rows into one column when other column data changes

Hi,

New to forum and hoping there is an answer to my quest

I have a sizable spreadsheet of customers and their contract numbers. Many customers have more than one contract. Each contract is on a separate row with the customer's information being repeated for each row.

The attached example shows one set of data in it's existing form with one row of customer information for each contract they own, whether it is one contract or more.

The next set of data on the same attachment shows what I would like to be my resulting data. That being, each customer having one row of data and their contract number or numbers in one cell of the customer's row of information. Multiple contract numbers would be separated with a comma and space.

The information will eventually be used in a Word mail merge to send one letter to each customer and list their contract number or numbers on their letter.

Any help would be greatly appreciated! I am a very good but basic-level Excel user.

Kerri

This should do it pretty quickly:

Please Login or Register  to view this content.

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.

Hi Kerri,

This "quickie" works to your specs:

Please Login or Register  to view this content.