CODEWITHSUNDEEP
excelrowstranspose
Carter Masterson
Carter Masterson

Reputation: 103

Transpose address data in three rows to columns?

I have data that looks like this:

Pharmacy
200 Main Street
San Diego, CA 11111
Liquor store
315 South 4th
Minot, ND 22222
County courthouse
111 West Hwy 45
West Allis, WI 33333
Bowling alley
16542 North Park St
Huntsville, AL 01010
...

But I want it to look like this:

Name                 Address           City/state
Pharmacy         200 Main Street    San Diego, CA 11111
Liquor store      315 South 4th       Minot, ND 22222
...

The data continues in this fashion from A1:A1620. I need the data transposed from this format into three variables: name, address, city/state. For example, "Pharmacy" would be the name for the first case, "200 Main Street" would be its address, and "San Diego, CA 11111" would be its city/state.

Upvotes: 0

Views: 1351

Answers (1)

BruceWayne
BruceWayne

Reputation: 23283

How's this - it's a VBA solution. (In Excel, right click your sheet name, and click "View Code" and place this in there):

Sub fixText()
Dim i As Integer, lastRow As Integer
Dim addressCol As Integer, cityCol As Integer

'Insert header row and add headers
Rows(1).EntireRow.Insert
Cells(1, 1).Value = "Name"
Cells(1, 2).Value = "Address"
Cells(1, 3).Value = "City/State"
addressCol = 2
cityCol = 3


'Find the last used row
lastRow = ActiveSheet.UsedRange.Rows.Count

For i = 2 To lastRow
    If i > lastRow Then Exit For
    Cells(i, addressCol).Value = Cells(i, 1).Offset(1, 0).Value
    Cells(i, cityCol).Value = Cells(i, 1).Offset(2, 0).Value

    ' Delete the two rows you just copied info from
    Range(Cells(i + 1, 1), Cells(i + 2, 1)).EntireRow.Delete

    'Get new last row
    lastRow = ActiveSheet.UsedRange.Rows.Count
Next i

End Sub

Note: I assume your data starts in A1, and is in one big block, so the last used row is pertinent data. Please let me know if this needs tweaking!

Upvotes: 1

Related Questions