Jun 09 2022 06:42 PM
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
Jun 09 2022 11:00 PM - edited Jun 09 2022 11:01 PM
@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.
Jun 09 2022 11:10 PM
Jun 09 2022 11:44 PM
@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.
Jun 10 2022 02:00 AM
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.
Jun 10 2022 05:45 PM
Jun 10 2022 05:48 PM