Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a PDF file programatically from Excel | Excel Discussion (Misc queries) | |||
File asks to update when no links were created | Links and Linking in Excel | |||
File is locked for Editing by user problem | Excel Discussion (Misc queries) | |||
format question when open csv file | Excel Discussion (Misc queries) | |||
Convert text file to MS_Excel | Excel Discussion (Misc queries) |