Hi -
In my data, there are 5 columns, CAR, TRUCK, NAME, CITY, STATE. The first
two columns will have a X in it if the person has a CAR or TRUCK. With the
data I am pulling from, if the same person has both a Car and Truck, they
will show up twice in seperate rows. I would like to find when a person has
both and combine it to one row (with a X in each CAR and TRUCK). So from:

CAR TRUCK NAME CITY STATE
X Mike Orlando FL
X Mike Orlando FL

To:

CAR TRUCK NAME CITY STATE
X X Mike Orlando FL

Help please...I am stuck.
Thanks,
Mike

Sub combinerows()

Const CarCol = "A"
Const TruckCol = "B"
Const NameCol = "C"
Const CityCol = "D"
Const StateCol = "E"

RowCount = 2
Do While Not IsEmpty(Cells(RowCount + 1, NameCol))
If Cells(RowCount, NameCol) = _
Cells(RowCount + 1, NameCol) And _
Cells(RowCount, CityCol) = _
Cells(RowCount + 1, CityCol) And _
Cells(RowCount, StateCol) = _
Cells(RowCount + 1, StateCol) Then

If IsEmpty(Cells(RowCount, CarCol)) Then
Cells(RowCount, CarCol) = _
Cells(RowCount + 1, CarCol)
End If
If IsEmpty(Cells(RowCount, TruckCol)) Then
Cells(RowCount, TruckCol) = _
Cells(RowCount + 1, TruckCol)
End If

Cells(RowCount + 1, "A").EntireRow.Delete
End If

RowCount = RowCount + 1
Loop

End Sub


"Mike R." wrote:

Hi -
In my data, there are 5 columns, CAR, TRUCK, NAME, CITY, STATE. The first
two columns will have a X in it if the person has a CAR or TRUCK. With the
data I am pulling from, if the same person has both a Car and Truck, they
will show up twice in seperate rows. I would like to find when a person has
both and combine it to one row (with a X in each CAR and TRUCK). So from:

CAR TRUCK NAME CITY STATE
X Mike Orlando FL
X Mike Orlando FL

To:

CAR TRUCK NAME CITY STATE
X X Mike Orlando FL

Help please...I am stuck.
Thanks,
Mike

Hi - thanks for the reply. For some reason, this does not seem to work.
More help please. I really appreciate it.
Mike

"Joel" wrote:

Sub combinerows()

Const CarCol = "A"
Const TruckCol = "B"
Const NameCol = "C"
Const CityCol = "D"
Const StateCol = "E"

RowCount = 2
Do While Not IsEmpty(Cells(RowCount + 1, NameCol))
If Cells(RowCount, NameCol) = _
Cells(RowCount + 1, NameCol) And _
Cells(RowCount, CityCol) = _
Cells(RowCount + 1, CityCol) And _
Cells(RowCount, StateCol) = _
Cells(RowCount + 1, StateCol) Then

If IsEmpty(Cells(RowCount, CarCol)) Then
Cells(RowCount, CarCol) = _
Cells(RowCount + 1, CarCol)
End If
If IsEmpty(Cells(RowCount, TruckCol)) Then
Cells(RowCount, TruckCol) = _
Cells(RowCount + 1, TruckCol)
End If

Cells(RowCount + 1, "A").EntireRow.Delete
End If

RowCount = RowCount + 1
Loop

End Sub


"Mike R." wrote:

Hi -
In my data, there are 5 columns, CAR, TRUCK, NAME, CITY, STATE. The first
two columns will have a X in it if the person has a CAR or TRUCK. With the
data I am pulling from, if the same person has both a Car and Truck, they
will show up twice in seperate rows. I would like to find when a person has
both and combine it to one row (with a X in each CAR and TRUCK). So from:

CAR TRUCK NAME CITY STATE
X Mike Orlando FL
X Mike Orlando FL

To:

CAR TRUCK NAME CITY STATE
X X Mike Orlando FL

Help please...I am stuck.
Thanks,
Mike

Hi - I have figured it out. The code works when I sort by name. Thanks!
Mike

"Mike R." wrote:

Hi - thanks for the reply. For some reason, this does not seem to work.
More help please. I really appreciate it.
Mike

"Joel" wrote:

Sub combinerows()

Const CarCol = "A"
Const TruckCol = "B"
Const NameCol = "C"
Const CityCol = "D"
Const StateCol = "E"

RowCount = 2
Do While Not IsEmpty(Cells(RowCount + 1, NameCol))
If Cells(RowCount, NameCol) = _
Cells(RowCount + 1, NameCol) And _
Cells(RowCount, CityCol) = _
Cells(RowCount + 1, CityCol) And _
Cells(RowCount, StateCol) = _
Cells(RowCount + 1, StateCol) Then

If IsEmpty(Cells(RowCount, CarCol)) Then
Cells(RowCount, CarCol) = _
Cells(RowCount + 1, CarCol)
End If
If IsEmpty(Cells(RowCount, TruckCol)) Then
Cells(RowCount, TruckCol) = _
Cells(RowCount + 1, TruckCol)
End If

Cells(RowCount + 1, "A").EntireRow.Delete
End If

RowCount = RowCount + 1
Loop

End Sub


"Mike R." wrote:

Hi -
In my data, there are 5 columns, CAR, TRUCK, NAME, CITY, STATE. The first
two columns will have a X in it if the person has a CAR or TRUCK. With the
data I am pulling from, if the same person has both a Car and Truck, they
will show up twice in seperate rows. I would like to find when a person has
both and combine it to one row (with a X in each CAR and TRUCK). So from:

CAR TRUCK NAME CITY STATE
X Mike Orlando FL
X Mike Orlando FL

To:

CAR TRUCK NAME CITY STATE
X X Mike Orlando FL

Help please...I am stuck.
Thanks,
Mike