merge duplicate lines while appending unique data

Hello all,
I have an excel grid with thousands of rows and 21 columns of data. Most of the rows have duplicate data, and I want to remove the dupes. The problem I have is:
1. the last 6 columns have unique data, and
2. I need to keep that data.

Is there a way to merge the lines and append unique data after the 21 base columns?

I have attached an excel grid which details what I am trying to do. There are 2 tabs describing what I have now (Before) and what I am trying to achieve (After). I am using Excel 2007.

Any assistance would be greatly appreciated. Thanks!

Thank you very much for the reply. I am actually preparing my data to be imported into a very fussy software program. The program requires that all claim lines be on 1 line; not multiple lines.

I will of course be saving a copy of my original data before proceeding.

Oh, okay.

In that case, let me figure it out.

My out put is not exactly the same as yours, but this is probably due to the way you copied them manually. The code overwrites the existing data, so there is not separate sheet out put. Try it any way.

Okay, here it is. Just run the macro while active sheet is the sheet that you want to reorganize.

The procedure doesn't reenter column headers to the first row, I hope that's not a problem.

Thank you very much to both of you! I don't have access to the real data until tomorrow morning, but I tried both of your macros with the sample data. They both great!

Thank you very much! The macro worked perfectly.

Tiger,
You are welcome!
Could you please now close this thread as solved. Go in to the top right -hand side of this page, select "Thread Tools", pick up Solved from the drop down menu.