Parsing Data from One Column to Multiple Columns

[TimStrickland]

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

[#2]

@TimStrickland You ask for a quick way. If you don't mid a solution that is both quick and dirty, please see attached and try to apply the same principle to your real data.

[#3]

@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?

[#4]

@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.

[#5]

@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.

[#6]

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.

[#7]

Unfortunately, the data is on a corporate laptop and I’m not easily able to justify to my IT department downloading/installing PowerQuery software. But thank you very much for the suggestion.