Forum Discussion

TimStrickland's avatar
TimStrickland
Copper Contributor
Jun 10, 2022

Parsing Data from One Column to Multiple Columns

Hi all… Looking for advice

I’m trying to find a quick way to sort data from one column into multiple columns in Excel 2013.
Data appears in the first column with a single record on multiple rows. As an example, Name in cell A1, Street in cell A2, City in A3, State in A4 and so on. Beneath that record other records follow the same pattern. 
What is the best way to move this data into column B for AName, C for Street, D for City and so on?

Any advice will be greatly appreciated. 
Thank you in advance. 
TimStrickland

excel
Formulas and Functions

6 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jun 10, 2022

    TimStrickland 

    Sub sort_into_columns()

Dim i As Long
Dim j As Long
Dim k As Long
Dim l As Long
Dim m As Long
Dim MaxRows As Long

Range("B:BB").Clear

m = Application.InputBox("How many columns are you going to fill?")

MaxRows = Range("A" & Rows.Count).End(xlUp).Row
j = 2
k = 1
For j = 2 To m + 1
For i = 1 To MaxRows / m

Cells(i, j).Value = Cells(k + l, 1).Value
k = k + m

Next i
k = 1
l = l + 1

Next j

End Sub

    You can try these lines of code. After starting the macro you only have to enter the number of columns or the number of multiple rows for a single record respectively.

    • TimStrickland's avatar
      TimStrickland
      Copper Contributor
      Jun 11, 2022
      Thank you OliverScheurich. I was honestly hoping for a function/formula that might help. I’m not particularly skilled with coding. But thank you very much for your response. I’ll give it a try and see what I can manage.
    • TimStrickland's avatar
      TimStrickland
      Copper Contributor
      Jun 10, 2022
      Riny_van_Eekelen, thanks for responding. In the attachment, I see you have visualized what I want to accomplish. I’d like to know if there is a function/formula or macro that allows me to automate the sorting of data. Do you have any idea how to do it?
      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Jun 10, 2022

        TimStrickland I'm not a VBA/Macro person, so I would go for PowerQuery if I were to automate this. You mention to be using Excel 2013, so you'd have to install the PQ add-in and learn PowerQuery. Neither learning VBA or PowerQuery is going to be a quick. It will take some time.

         

        Perhaps someone else here can write a macro for you that is easily adopts to your real life (and perhaps changing) data.

Resources