Change data from a row to a column

Occasional Visitor

I have a column in access and/or excel as follows:



1.      123456, 456789, 452123, 741852 or can remove commas between each number.

2.      987456, 521364, 796321, etc.

I am trying to convert this column to:


1.       123456

2.        456789

3.       452123

4.       741852

5.       987456

6.       521364

7.       ETC.


Can anyone help. I originally received this data in a excel worksheet with commas between each number. I can take out the commas if needed. I have also copied and pasted the column in a table in access.


Thanks Tom


3 Replies

I will try to come up with a sample function to do this, but the approach is going to be based on the Split() function to place each of the values in a row into an array. Take a few minutes, please, to read about the Split() function. I'll see how quickly I can do it, but it may be a little while.

@George Hepworth

I created two tables, one for the source data and one for the output data. They are identical with two fields: Row and PCFN.


This function splits the comma delimited source rows into an array then then extracts the items from the array into the output table, one row per item.



Public Sub RowToColumn()

On Error GoTo errHandler
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim arySourceRow() As String
Dim lngRow As Long
Dim i As Integer

lngRow = 1
Set db = CurrentDb
Set rst = db.OpenRecordset(Name:="SELECT Row, PCFN FROM SampleTable", Type:=dbOpenDynaset)
With rst
Do Until .EOF
arySourceRow = Split(!PCFN, ",")
For i = 0 To UBound(arySourceRow())
db.Execute "INSERT INTO FinalData (Row, PCFN) SELECT " & lngRow & ", " & arySourceRow(i), dbFailOnError
lngRow = lngRow + 1
Next i
End With


On Error Resume Next
Set db = Nothing
Set rst = Nothing


Exit Sub


MsgBox Prompt:="Error " & Err.Number & " (" & Err.Description & ") in procedure RowToColumn, line " & Erl & ".", _
Buttons:=vbCritical + vbOKOnly, _
Title:="Something Bad Happened"
Resume Cleanup

End Sub



@ygoshy I created a sample relational database application to illustrate this approach. It's on my public OneDrive.