combine details on rows

[Sarah (OGI)]

I have a spreadsheet which has been extracted and imported from an external
source. The first row shows headings. Each subsequent row relates to a
client and depending on the information, the details are shown on separate
rows but never in the same column

Example:
A,B,C,D,E,F,G,H,I
ClientRef,Name,Address,PostCode,Birthdate,Occupation,Status,CarMake,CarModel
BLJ1,Joe Bloggs,The House,DY13 9LT,,,,AUDI,A4
BLJ1,Joe Bloggs,The House,DY13 9LT,10/06/1982,Consultant,Employed,,,

I need to combine the details so each ref appears only once and all the
relevant details are shown along the one row.

Any ideas?

 

[Dave Peterson]

So each column in each group only has a single entry--but it could be on a
different row?

If yes:

Option Explicit
Sub testme()
Dim FirstRow As Long
Dim LastRow As Long
Dim wks As Worksheet
Dim FirstCol As Long
Dim LastCol As Long
Dim iCol As Long
Dim iRow As Long
Dim FoundAMatchingRow As Boolean

Set wks = Worksheets("sheet1")

With wks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

FirstCol = 5 'first 4 columns are used to match up rows

For iRow = LastRow To FirstRow + 1 Step -1
FoundAMatchingRow = True
For iCol = 1 To FirstCol - 1
If .Cells(iRow, iCol).Value <> .Cells(iRow - 1, iCol).Value Then
FoundAMatchingRow = False
Exit For
End If
Next iCol
If FoundAMatchingRow = True Then
For iCol = FirstCol To _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column
If .Cells(iRow, iCol).Value <> "" Then
'move it up
.Cells(iRow - 1, iCol).Value = .Cells(iRow, iCol).Value
End If
Next iCol
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

I assumed that the first 4 columns are used to determine if the rows match and
should be processed together.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm