Excel data into one column

Posted on

QUESTION :

I work for a charity and not too familiar with excel.

The data is is imported from our website into a spreadsheet but the orders are not separated into rows and columns as I would like. Is it possible to arrange multiple data in rows into one column? Not every row has the same number of entries.I have used text to columns to convert csv data into columns.

Thank you

enter image description here

Edit

This is how the import appears on the csv file:

Orders Product Name
50, 30 water aid, food parcel
5 general
20 medical
1, 5, 20, 100 general, water aid, medical, food

AND This is what I would like it to look like.

Orders Product name
50 water aid
30 food parcel
5 general
20 medical
1 general
5 water aid
20 medical
100 food

Really not sure how i can import the csv format here, anyone help?

ANSWER :

VBA Macro solves the issue:

enter image description here

How it works:

  • Press Alt+F11 to open VB editor.
  • Copy & Paste this code as standard module.

    Sub SplitData()

    Dim xRg As Range
    Dim xRg1 As Range
    Dim xCell As Range
    Dim i As Long
    Dim xAddress As String
    Dim xUpdate As Boolean
    Dim xRet As Variant

    On Error Resume Next

    xAddress = Application.ActiveWindow.RangeSelection.Address
    Set xRg = Application.InputBox("Please select a column", "Split Data", xAddress, , , , , 8)
    Set xRg = Application.Intersect(xRg, xRg.Worksheet.UsedRange)

    If xRg Is Nothing Then Exit Sub

        If xRg.Columns.Count > 1 Then
            MsgBox "You can't select multiple columns", , "Split Data"
            Exit Sub
            End If

            Set xRg1 = Application.InputBox("Split to (select single cell):", "Split Data", , , , , , 8)
            Set xRg1 = xRg1.Range("A1")

            If xRg1 Is Nothing Then Exit Sub
                xUpdate = Application.ScreenUpdating
                Application.ScreenUpdating = False

                For Each xCell In xRg
                    xRet = Split(xCell.Value, ",")
                    xRg1.Worksheet.Range(xRg1.Offset(i, 0), xRg1.Offset(i + UBound(xRet, 1), 0)) = Application.WorksheetFunction.Transpose(xRet)
                    i = i + UBound(xRet, 1) + 1
                Next
                Application.ScreenUpdating = xUpdate
            End Sub

  • Hit Alt+Q to return to Sheet.
  • Save file as, Macro Enabled (.xlsm ).
  • RUN the Macro.
  • Select required cells from Col A.

enter image description here


  • Finish with Ok.

enter image description here

  • Select single cell for OUTPUT & finish with Ok.

enter image description here


  • RUN the Macro again and repeat the procedure for Product column.
  • You get final output.

enter image description here

N.B. This macro works for, one column at a time.

Leave a Reply

Your email address will not be published. Required fields are marked *