Vertical List to Horizontal Database

[GradyLorenzo]

I have a list formatted as follows:

John Doe
someStreet
someZip
someState

Jane Doe
someStreet
someZip
someState

Jack Doe
someStreet
someZip
someState

How would I convert this into a format that can be imported into PHP MyAdmin? I would type it one at a time, but the 60k+ entries is a bit intimidating for obvious reasons...

 

[Willmonbo]

have you tried the Paste Special and transpose method?

 

[GradyLorenzo]

I'll be honest, I have little experience with Excel. I have more experience with Dreamweaver, Photoshop, 3Ds Max, Unity...but not much with Excel...so I don't exactly know what that means...

 

[MrKowz]

Welcome to the forums!

Assuming your data starts in A1, try running this macro:

Public Sub GradyLorenzo()
Dim i   As Long, _
    j   As Long, _
    LR  As Long
 
LR = Range("A" & rows.Count).End(xlUp).row
Application.ScreenUpdating = False
For i = 1 To LR Step 5
    Application.StatusBar = "Currently on row " & i & " of " & LR
    For j = 1 To 3
        Range("A" & i).Offset(0, j).Value = Range("A" & i).Offset(j, 0).Value
        Range("A" & i).Offset(j, 0).ClearContents
    Next j
Next i
Range("A1:A" & LR).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

To use this macro, while in your spreadsheet press ALT+F11 to open the VBA editor. Then go to Insert>Module. Copy/paste the above code into the module. Then go back to your spreadsheet and press Alt+F8 to open the Run Macro window. Choose the macro from the list, and press "Run".

Excel will appear to be frozen, but in the bottom left corner of Excel, you should be able to monitor this macro's progression. Be sure to back up your data prior to running macros, as they cannot be "undone".

 

[MrKowz]

This macro will do the same thing, but should run slightly faster:

Public Sub GradyLorenzo()
Dim i   As Long, _
    j   As Long, _
    LR  As Long
    
LR = Range("A" & rows.Count).End(xlUp).row
Application.ScreenUpdating = False
For i = 1 To LR Step 5
    Application.StatusBar = "Currently on row " & i & " of " & LR
    Range(Cells(i + 1, 1), Cells(i + 3, 1)).Copy
    Range("B" & i).PasteSpecial Paste:=xlPasteValues, Transpose:=True
    Range(Cells(i + 1, 1), Cells(i + 3, 1)).ClearContents
Next i
Range("A1:A" & LR).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

 

[GradyLorenzo]

If I use that macro, it runs through each row (shown in the status bar), very rapidly I might add, doing something I have no way of telling what this "something" is because when the whole process is complete, everything is just gone. I have both Excel and OpenOffice, so whatever works will do perfectly...

Is there a way I can just put a comma at the end of each line?

 

[MrKowz]

Can you please describe to me where this data is (perhaps a screenshot of the upper left corner of the data)? The macro is very dependant on the location and layout of the data.

 

[GradyLorenzo]

I can provide a link to the Excel file

 

[MrKowz]

Unfortunately, my workplace prevents me from downloading files. If you would like, post the link and I will try to remember to check it out when I get home tonight.

Please note that if the Excel file contains any sensitive/confident information, it would be strongly advised not to publicly post the link. In this case, feel free to PM it to me.

 

[GradyLorenzo]

Thanks much.

www.maildropfraud.com/html/maidropdata-ak-ga.xls