Reorganizing Data

Here is the macro you can copy:

Sub CompressData2()
Dim iTarget As Range
Dim J As Integer
Dim iTargetCols As Integer

iTargetCols = Val(InputBox("How many columns?"))
If iTargetCols < 2 Or Selection.Columns.Count > 1 Then Exit Sub

Set iTarget = Selection.Cells(1, 2).Resize(2, iTargetCols)

For J = 1 To Selection.Rows.Count
    iTarget.Cells(J) = Selection.Cells(J)
Next J
Selection.Delete
End Sub

The macro of this tip does a fine job but the definitions of the rSource and rTarget ranges are rather complex.

The rSource range needs even not to be defined because it is simply the Selection property.

While experimenting a bit I discovered that for the rTarget range it suffices to define a 2 rows range with the wanted number of columns. When VBA needs more rows, it simply adds them following the same pattern (number of columns). So, the definition could be reduced from 3 to 1 line:
Set iTarget = Selection.Cells(1, 2).Resize(2, iTargetCols).

The result is this smaller macro that does the same job:

(see Figure 1 below)

Macros are OK, but there are a few "new & improved" ways to do the same thing. And, some of them are much more wysiwyg than a macro.

The general concept that the idea of "reorganizing data" can be lumped under is "data cleaning".

A more specific search term is "unstack".

Unstack Data that is Separated by Bold Text: Challenge w/Kevin Lehrbass - Formulas, VBA, Power Query
https://www.youtube.com/watch?v=JsErvkFvYf8
Oz Du Soleil
Problem: a tall column of data that needs to be unstacked and converted into neat columns and rows.
Twist: the distinction where the list needs to be divided is by bold font.
(Download the workbook: http://datascopic.net/unstackingboldfont )
Parents' names are bold.
Childrens' names are regular font.
How does this list get unstacked with the children next to the right parent?
I open with Excel's Get and Transform (Power Query).
a formula-based solution and
a VBA solution!

Dynamic Array: Unstack Data with SEQUENCE ... just 2 steps
https://www.youtube.com/watch?v=ODOSw1-w5Ws
Oz Du Soleil
I was playing around more with the Dynamic Array functions, especially after Leila Gharani posted her video that made unstacking data pretty easy with a substitution trick and finding a pattern in the dataset. Here is her video: https://www.youtube.com/watch?v=rscXN...
Well, I found a 2-step process that REALLY simplifies unstacking a column of data. SEQUENCE, COUNTA and INDEX get us there
Alas, not everyone has the Dynamic Arrays yet. If you don't have them, and you need to unstack some data, use Leila's method.

Excel Dynamic Arrays: Unstack a Column of Records
https://www.youtube.com/watch?v=dy1DwE-B9ug
Oz Du Soleil
The classic problem: unstack a column of records in Excel. This video shows a cool way to get this stuff un-stacked. You'll see:
. * the FILTER Dynamic Array function,
. * the SEQUENCE Dynamic Array Function, and even
. * the SORTBY.

How to convert Stack Data into Unstack different approaches | Unstack Uneven Data Excel Power Query
https://www.youtube.com/watch?v=X6XTTxjAg14
. * using tables and formula,
. * PowerQuery
. * the new Dynamic Array formula functions

Note: many of these ideas are limited to Excel 2021 or 365 because they use new features and functions
.
Move Data from One Column into Multiple Columns in Excel 365 (Array Formula)
https://excelmee.com/excel-formulas/move-data-from-one-column-into-multiple-columns-excel-365/
Step 1 (Lookup Value)
Step 2 (Lookup Array)
Step 3 (Return Array)
Final Step (XLOOKUP Formula to Unstack Data)

@To whom it may concern.
The same task can be achieved with a simple formula - as shown in the linked picture:
http://s14.postimg.org/jq7n3p6fl/NONAME.png
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016)
ISRAEL

This "worked" on a PowerPoint table copied from a PDF in that it sorted it into the requisite number of columns but it didn't take account of some columns having more than one entry in the original with the result that the results got skewed.

When I tried it on a more normal table of figures it crashed excel, getting stuck in a loop asking me how many columns I wanted.

I have tested this code and cannot get it to work for me - all it does is delete the info from the first cell of the column containing data.

Thanks for this hint. In addition, the "indirect()" function enables to do this without a macro by populating a table by "=indirect("A" & <whatever you need for calculating the number of the original line corresponding to your target cell>)". Then, if necessary, one can copy the values for getting rid of the initial column data.

It's a little confusing what's going on here and why you'd ever need to organize data in this way. A picture is worth 1000 words, and a concrete example is worth at least a couple hundred.