Excel split the column data and add into new row

Question

I am used to working with python but for this need to do it in excel.

I have input data that looks like in I/P data and I would like to get the data as mentioned in the Desired output. The desired output should just replace the data into column A and B. There are many other columns as well.

Any lead on how to do in excel/VBA would be nice.

How long is the range to be processed? In terms of 100, 1000, 20000 or more... According to that size it is good to directly manipulate the range, or use arrays. — FaneDuru, Mar 05 '20 at 13:44
In terms of columns A:R and rows: 1652. But the number of rows could be varying — Rocky, Mar 05 '20 at 13:47
OK. A:R does not matter to much, since only the data in columns A:B will be processed. If the processed data will be returned in column D:E, will that be a problem? Do you need to do that in another worksheet, or how? The separator on the B:B column sells value will always be "; ", or is it possible to also meat only ";"? — FaneDuru, Mar 05 '20 at 13:49
It can be replaced on the same column A and B or a new column can be added after A and B. Would that be possible? — Rocky, Mar 05 '20 at 13:52
You can start by doing a `Text to Columns` using the semicolon and the space as delimiters to split the orders into individual cells. No need for vba to do that. — Stavros Jon, Mar 05 '20 at 13:52
Yes, but the wise way, I think, would be to avoid messing up the existing data range. At least for the testing purpose. And the processed data do be put somewhere else... — FaneDuru, Mar 05 '20 at 13:53
@StavrosJonYes, I know that. But this would add 123,456 into a new column. I want them to be in the same column underneath each other. — Rocky, Mar 05 '20 at 13:57
The idea is, you can cange the destination whenever you need. The code will be simple enough (but efficient, I think) to be understood. And, please note, that here we do not write code for people asking questions. We usually help the to solve their problems in code and learn something... But I have some time now and I will post an answer in some minutes... — FaneDuru, Mar 05 '20 at 14:00

Error 1004 · Accepted Answer · 2020-03-05T14:58:27.367

Try the below:

Codes:

Write a line for every order of every Mr no (maybe small down your code)

Sub test()

Dim LastRowA As Long, LastRowD As Long, i As Long, y As Long
Dim Mr_No As String
Dim arrInput As Variant, arrValues As Variant

With ThisWorkbook.Worksheets("Sheet1")

    'Let as assume that Column A includes the letters. Find LastRow
    LastRowA = .Cells(.Rows.Count, "A").End(xlUp).Row
    arrInput = .Range("A2:B" & LastRowA)

    For i = LBound(arrInput) To UBound(arrInput)

        Mr_No = arrInput(i, 1)
        arrValues = Split(arrInput(i, 2), ";")

        For y = LBound(arrValues) To UBound(arrValues)
            LastRowD = .Cells(.Rows.Count, "D").End(xlUp).Row + 1
            .Range("D" & LastRowD).Value = Mr_No
            .Range("E" & LastRowD).Value = Trim(arrValues(y))
        Next y

    Next i

End With

End Sub

Store values in an array an print the whole array at the end (faster)

Sub test()

Dim LastRowA As Long, LastRowD As Long, i As Long, y As Long, arrCounter As Long
Dim Mr_No As String
Dim arrInput As Variant, arrValues As Variant
Dim arrResults() As Variant

arrCounter = 0

With ThisWorkbook.Worksheets("Sheet1")

    'Let as assume that Column A includes the letters. Find LastRow
    LastRowA = .Cells(.Rows.Count, "A").End(xlUp).Row
    arrInput = .Range("A2:B" & LastRowA)

    For i = LBound(arrInput) To UBound(arrInput)

        Mr_No = arrInput(i, 1)
        arrValues = Split(arrInput(i, 2), ";")

        For y = LBound(arrValues) To UBound(arrValues)

            ReDim Preserve arrResults(1, arrCounter)

            arrResults(0, arrCounter) = Mr_No
            arrResults(1, arrCounter) = Trim(arrValues(y))

            arrCounter = arrCounter + 1

        Next y

    Next i

    .Range("D2:E" & arrCounter + 1).Value = WorksheetFunction.Transpose(arrResults)

End With

End Sub

Results:

@Rocky i have import another solution which i think i faster. You could try both! Hope you find what you are looking for! — Error 1004, Mar 05 '20 at 14:59
Works perfectly! What is the best practice or sources to learn coding in excel? — Rocky, Mar 06 '20 at 05:54
@Rocky think of a real life scenario which is time consuming and hard to be done manually and start automation by writing some lines of code to achieve it. after mistakes, testing and research you will learn. Also you could start solving questions in forums be become more efficient. This is my opinion. — Error 1004, Mar 06 '20 at 07:43

FaneDuru · Answer 2 · 2020-03-05T14:11:14.663

Please use the next code. It is extremely fast, working only in memory (using arrays). It also allows the separator to be "; ", "; " (more spaces, but the editor will correct) or ";". My code put the result at once, and can be very rapidly process thousands of rows.

Sub SplitCells()
  Dim sh As Worksheet, lastR As Long, arrIn As Variant, arrRow As Variant
  Dim arrF As Variant, i As Long, j As Long, n As Long

    Set sh = ActiveSheet ' use here your sheet
    lastR = sh.Range("A" & Cells.Rows.Count).End(xlUp).row
    arrIn = sh.Range("A2:B" & lastR).value
    ReDim arrF(1 To 2, 1 To 2000): n = 1

    For i = 1 To UBound(arrIn, 1)
        arrRow = Split(Trim(arrIn(i, 2)), ";")
        For j = 0 To UBound(arrRow)
             arrF(1, n) = arrIn(i, 1)
             arrF(2, n) = arrRow(j)
             n = n + 1
        Next j
    Next i
    ReDim Preserve arrF(1 To 2, 1 To n - 1)
   sh.Range("D2").Resize(n - 1, 2) = WorksheetFunction.Transpose(arrF)
End Sub

If you want to return the array data in another range, it is enough to change "D2" with whatever you need...

Excel split the column data and add into new row

2 Answers2