Multiple rows, duplicate entries, mail merge

Having an issue where I am trying to use mail merge in congress with an Excel spreadsheet. The spreadsheet in question has a data dump from the database and lists mutiple rows of contact information. However some of the contacts have multiple Job descriptions, hence there are duplicate rows for some of these people. I am trying to find a way to combine the data from the multiple rows of these duplicates into one row so that mail merge will pull the data from it correctly. If that was as confusing as it feels, let me try an example.

In the attached spreadsheet example, I need mail merge to pull from one row, all of Mr. Pink's information. You will notice that he exists in the list three times, because the industry column is unique for each row. What I would like to do is somehow combine this information into a single row for Mr. Pink, and as many others as are represented in the data dump, so that mail merge will work.

Many thanks for any assistance,
RB

RB,

What I've done in the past is inserted a column next to the names and then a function to tell me if the name on the current row is the same as the one below.

Example of function entered in cell A2:

HTML Code: 

This presupposed that everything is already sorted by name (or whatever your identifier). Drag the formula out to the rest of the column, and then Copy, Paste Special, Values. You're ready to sort by your new column A and then delete the rows marked "Duplicate". Delete column A, and you should be in good shape.

Thanks cmcconnehey,

That determines if a row is a duplicate, but the other end of my problem is incorporating data from the duplicate rows into the original for a mail merge. Basically I want the raw data as it appears in example1.jpg to turn out looking like the data in example2.jpg. The highlighted cells are those I am most interested in.

Example 1
\1

Example 2
\1

Thanks again for your help!
RB

RB,

Sorry, I must have glanced over the part about combining the data when I read it the first time.

What I’ve done in the past is written a macro to copy all the data up onto one row. The basic logic you’ll use is as follows.

1. Sort by name, or unique identifier.
2. You’re going to start with your unique identifier on the first data row and compare it to the unique identifier in the row above it.
3. If the row being examined is the same as the same as the row above, select cells I and J of your current row, and copy the data.
4. Offset the active row so its up on the line above that which you just examined. Check to see if cell K is empty. If it is, paste the data you just copied. Otherwise keep offsetting the active cell one to the right until you find an empty one you can paste to.
5. Select the entire row below you (the one you were examining), and then delete it.
6. Jump back to step three and continue until you run out of data.

There are quite a few ways to do what you’re looking for, but this is the easiest that I know of. It just takes a couple “crawlers” to review and compare the data. You’ll probably want to break it down into a couple small subroutines to keep it simple. One that compares the two rows, one that does the copy/paste/delete. Below is an example of the code I used for the Copy/Paste portion of one of my other projects.

Be careful when using multiple subroutines that you define your variables well. As you pass them back and forth I've caused myself a few headaches not carefully watching the naming and how I'm dictating that they're set.

HTML Code: