Thank you both.

This is the code I'm using:
Sub formatSerialNum6digits()
'put all SerialNums (for rows where the recnum, last name,
'first name, and middle initial match the row above)
'into the SerialNum column of the first row.
Range("A2").Select
RowCount = 2
'As long as the current record number is the same as
'the next record number, do this process.
Do While Range("A" & (RowCount + 1)) < ""
'if SerialNum starts with 7, format with leading zero
If (Left(Range("B" & RowCount), 1) = 7 And _
(Len(Range("B" & RowCount) = 5))) Then
Range("B" & RowCount) = "QQQQQ0" & Range("B" & RowCount)
RowCount = RowCount + 1
Else
RowCount = RowCount + 1
End If
Loop
Range("A2").Select
RowCount = 2

Do While Range("A" & (RowCount + 1)) < ""
'set variable OldRecNum to the current record number
OldRecNum = Range("A" & RowCount)
'set variable OldSerialNum to current SerialNum
OldLastName = Range("C" & RowCount)

'set variable NewRecNum to the record number
'below the current record number
NewRecNum = Range("A" & (RowCount + 1))
'set variable NewRecNum to the record number
'below the current record number("A" & (RowCount + 1))
'set variable NewPLastName to the Last ("A" & RowCount)Name
'below the current Last Name
NewLastName = Range("C" & (RowCount + 1))
'if the Record Number in the current cell
'is the same as the one below, add the SerialNum in the
'cell below to the current SerialNum
If (OldRecNum = NewRecNum) And _
(OldLastName = NewLastName) Then
Range("B" & RowCount) = Range("B" & RowCount) & " " & Range("B" &
(RowCount + 1))
'delete the row below the current row
Rows(RowCount + 1).Delete
Else
RowCount = RowCount + 1
End If
Loop
'remove QQQQQ
Cells.Replace What:="QQQQQ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

------------------------------------------------------------------------------------------
Here's the data I'm using to test this
RecNum SerialNum LastName
3 71234 smith
3 319993 smith
3 455555 smith
3 1234567 smith
3 73339 smith
3 4239777 smith
3 77339922 smith
3 4444494 smith
3 777123 smith
5 9898989 jones
5 9999999 jones
5 3333333 jones
5 33338888 jones

-------------------------------------------------------------------------------------
When I'm finished, all the SerialNums should be in the same cell, separated
by a space, with the data consolidated, so that the data from each RecNum is
on only one row, as below.

RecNum SerialNum LastName
3 071234 319993 455555 1234567 073339 4239777 77339922 4444494 777123 smith
5 9898989 9999999 3333333 33338888 jones


"Rick Rothstein" wrote:

First of all, you do NOT have to add text to the beginning of your number
with QQQQQ0 only to replace your QQQQQ with the empty string later on... it
is just should not be necessary to do something like that. Why don't you
show us some examples of numbers you have and what you want them to look
like afterwards and we'll give you a more straightforward approach than the
one you are trying to implement.

--
Rick (MVP - Excel)