from horizontal to vertical database like

[shvoong]

Hi,

I have a data in rows and I want to convert it to vertical columns while keeping (and duplicatin) the Id columns in order to import it to my DB later.

Please see an example:
this is how I get the data:
id first last City zipCode
1 Dan Mano Ney-york 12345
2 Deby Roger Chicago
3 Ben Stiller Boston 4555

This is how I eventually want it to be:
Id ItemValue
1 dan
1 Mano
1 New-York
1 12345
2 Deby
2 Roger
2 Chicago
3 Ben
3 Stiller
3 Boston
3 4555

As you can see if there are blank cells I ignore them.

Is there a solution to my problem?

thanks in advance

Omer

[mrice]

This macro may help you. It assumes your data starts in A1. A new sheet is created with the output.

Sub Test()
Dim MyRow As Range
Dim N As Long
Dim Counter As Long
Dim MySheet As Worksheet
Dim TargetSheet As Worksheet
Set MySheet = ActiveSheet
Sheets.Add
Set TargetSheet = ActiveSheet
MySheet.Activate
For Each MyRow In Cells(1, 1).CurrentRegion.Rows
    For N = 2 To Cells(MyRow.Row, Cells.Columns.Count).End(xlToLeft).Column
        Counter = Counter + 1
        TargetSheet.Cells(Counter, 10) = Cells(MyRow.Row, 1)
        TargetSheet.Cells(Counter, 11) = Cells(MyRow.Row, N)
    Next N
Next MyRow
End Sub

[shg]

The format your data arrives in is much better suited to analysis in Excel than the format you want to convert it to.