Combining duplicate row data

[wagz]

I have a sheet where the rows contain duplicate column data. I would like to
combine the duplicate rows while combining the individual column data.
Example:

Name Address City State Zip Phone
John Smith 999 Onterio MI 12345
John Smith MI
1234567890
John Smith 999 Onterio 12345
John Smith MI

There are multiple columns and multiple rows in the real content. This is
just an example but this should get the point across.

Any help with this would be greatly appriciated. Thanks!

-EW

 

[Joel]

You would need to write a very simple macro to do this task, but I don't
think you will get the results your are expecting. Most cases when you have
data similar to what you are showing the duplicate data is the same column is
exactly the same. what do you do when one row has "999 Onterio " and the
next row has "999 Onterio St." and the 3rd row has "999 Onterio Street"? You
also haved problems when the name doesn't exactly match "John Smith", "John
B. Smith" John B Smith", or "Smith, John".

 

[wagz]

In this example the information could differ greatly, but in the application
the first row is always the same with no dynamics per category. In each
cateogry I would like to cobine the data where there are holes.

 

[Joel]

Sub CombineRows()

RowCount = 1
Do While Range("A" & RowCount) <> ""
If Range("A" & RowCount) = Range("A" & (RowCount + 1)) Then
For ColCount = 2 To 6
If Cells(RowCount, ColCount) = "" Then
Cells(RowCount, ColCount) = Cells(RowCount + 1, ColCount)
End If
Next ColCount
Rows(RowCount + 1).Delete
Else
RowCount = RowCount + 1
End If
Loop
End Sub