Flat File
 

I need help, I have an excel file that has Prov. Nums, Addresses,
and specialties, in multi-rows. I need to move the Addresses and Specialty
rows to be columns reflecting the Prov. Num.
For example:

Provider Num Address Specialty
125126 360 95th Internaml Medicine
125126 320 85th Cardiology

Change to:

Provider Num Address1 Specialty1 Address2 Specialty2
125126 360 95th Internaml Medicine 320 85th Cardiology

Max,

Here is one way

Dim cLastRow As Long
Dim i As Long
Dim rng As Range

cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("D1").Value = "Address 2"
Range("E1").Value = "Speciality 2"
For i = 2 To cLastRow Step 2
Cells(i + 1, "B").Resize(1, 2).Cut Destination:=Cells(i, "D")
If rng Is Nothing Then
Set rng = Cells(i + 1, "B").Resize(1, 2)
Else
Set rng = Union(rng, Cells(i + 1, "B").Resize(1, 2))
End If
Next i
If Not rng Is Nothing Then
rng.EntireRow.Delete
End If


--
HTH

-------

Bob Phillips
"Max" wrote in message
...
I need help, I have an excel file that has Prov. Nums, Addresses,
and specialties, in multi-rows. I need to move the Addresses and Specialty
rows to be columns reflecting the Prov. Num.
For example:

Provider Num Address Specialty
125126 360 95th Internaml Medicine
125126 320 85th Cardiology

Change to:

Provider Num Address1 Specialty1 Address2
Specialty2
125126 360 95th Internaml Medicine 320 85th Cardiology

Should I put this in a macro, or where? Thanks

"Bob Phillips" wrote:

Max,

Here is one way

Dim cLastRow As Long
Dim i As Long
Dim rng As Range

cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("D1").Value = "Address 2"
Range("E1").Value = "Speciality 2"
For i = 2 To cLastRow Step 2
Cells(i + 1, "B").Resize(1, 2).Cut Destination:=Cells(i, "D")
If rng Is Nothing Then
Set rng = Cells(i + 1, "B").Resize(1, 2)
Else
Set rng = Union(rng, Cells(i + 1, "B").Resize(1, 2))
End If
Next i
If Not rng Is Nothing Then
rng.EntireRow.Delete
End If


--
HTH

-------

Bob Phillips
"Max" wrote in message
...
I need help, I have an excel file that has Prov. Nums, Addresses,
and specialties, in multi-rows. I need to move the Addresses and Specialty
rows to be columns reflecting the Prov. Num.
For example:

Provider Num Address Specialty
125126 360 95th Internaml Medicine
125126 320 85th Cardiology

Change to:

Provider Num Address1 Specialty1 Address2
Specialty2
125126 360 95th Internaml Medicine 320 85th Cardiology

I put is in VB Editior, and it gives me an error in the line (xlUp). Please
advise, I appreciated it.

"Max" wrote:

I need help, I have an excel file that has Prov. Nums, Addresses,
and specialties, in multi-rows. I need to move the Addresses and Specialty
rows to be columns reflecting the Prov. Num.
For example:

Provider Num Address Specialty
125126 360 95th Internaml Medicine
125126 320 85th Cardiology

Change to:

Provider Num Address1 Specialty1 Address2 Specialty2
125126 360 95th Internaml Medicine 320 85th Cardiology

It worked Thanks

"Bob Phillips" wrote:

Max,

Here is one way

Dim cLastRow As Long
Dim i As Long
Dim rng As Range

cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("D1").Value = "Address 2"
Range("E1").Value = "Speciality 2"
For i = 2 To cLastRow Step 2
Cells(i + 1, "B").Resize(1, 2).Cut Destination:=Cells(i, "D")
If rng Is Nothing Then
Set rng = Cells(i + 1, "B").Resize(1, 2)
Else
Set rng = Union(rng, Cells(i + 1, "B").Resize(1, 2))
End If
Next i
If Not rng Is Nothing Then
rng.EntireRow.Delete
End If


--
HTH

-------

Bob Phillips
"Max" wrote in message
...
I need help, I have an excel file that has Prov. Nums, Addresses,
and specialties, in multi-rows. I need to move the Addresses and Specialty
rows to be columns reflecting the Prov. Num.
For example:

Provider Num Address Specialty
125126 360 95th Internaml Medicine
125126 320 85th Cardiology

Change to:

Provider Num Address1 Specialty1 Address2
Specialty2
125126 360 95th Internaml Medicine 320 85th Cardiology