Transforming a column into a table

Contents

Imagine that after unloading from some corporate ERP system, database or program, you received data in the form of a long column, and in order to work you need to compose a normal two-dimensional table from them:

Obviously, doing this manually is very long and dreary, so let’s look at a few ways to do it beautifully.

Method 1. Formulas

If you look closely at the source data, you can see a clear system – the column is divided into blocks of 7 cells, each of which we need to transform into the corresponding row. To do this, you need to bind to row numbers in the source table. Every seventh line (1,8,15…) is the date of the deal. One line down is the name of the manager. One more lower – the city, etc. For simplicity and clarity, let’s add numbering to the left and top to the blank of the future table:

The numbers on the left (1,8,15…) are the line numbers that start each block. The numbers on top (0,1,2,3…) are the offset within the block. Now, to get the addresses of the cells we need, we can enter a simple formula that glues the column letter with the row number:

Pay attention to the correct fixing of rows and columns with dollar signs – this is important when copying the link from the first cell to the entire green range.

And it remains to turn our text link into a full-fledged one. The function will help us with this. INDIRECT (INDIRECT), about which I have already written in detail:

Voila.

Method 2. Power Query

Power Query is a free Excel add-in created by Microsoft. For Excel 2010-2013, it can be completely free to download and install (the Power Query tab will appear), and in Excel 2016 it is already built-in by default and all its tools are on the tab Data (Date). This add-in can import data into Excel from almost any source and then transform it in any desired way.

First, let’s turn our range into a “smart table” – for this you need to select the data and press the keyboard shortcut Ctrl+T or select from the tab Home Command Format as a table (Home — Format as Table). In the window that appears next, it is important to uncheck Table with headers (My table has headers), because We don’t have a hat.

Once loaded, our data will appear in a new Power Query window:

Now we add an index column to the data (i.e., in fact, we imitate row numbering) through the tab Adding a Column – Index Column (Add Column — Index Column):

And then add a column where we display the remainder of dividing the index by 7 using the command Adding a column – Standard – Remainder of division (Add Column — Standard — Mod):

Now let’s collapse our table by converting the numbers in the resulting column into new column headings. For this we use the command Pivot column tab Convert (Transform — Pivot Column):

It remains to fill the resulting voids (null) in all columns except the last two with the command fill down tab Convert (Transform — Fill Down):

Delete unnecessary columns (first and last) and filter out extra rows (uncheck Null in the filter by column 5). Column headings can be renamed by double-clicking for more convenience, and you can also set up suitable data formats using the icons in the header:

The finished table is uploaded to a new or any of the existing sheets using the command Home — Close and load — Close and load as (Home — Close&Load — Close&Load to):

In the future, if the original data table changes in size or content, it will be enough to simply update our query by right-clicking on the final table and selecting the command Update & Save (Refresh).

Method 3: Macro from the PLEX add-on

If you have my PLEX add-in for Excel 2007-2016 installed, you can do everything even faster and easier. Select the source data and click on the tab PLEX – Transform – Resize (Transform — Resize):