How To Reshape Data From Long To Wide In Excel

@GibbE155

Since you said "hundreds of thousands", I recommend you lot don't use formulas for this, just instead use Power Query.

Select any cell in your information and utilize Data>Get & Transform Data>From Table/Range

This volition open up the Ability Query Editor.

Now, utilize Add Column>Full general>Custom Column and employ this formula:

          "Sample " & Number.ToText([Sample])

Phone call the new column ColumnHeader (not essential, y'all can call it whatever you want).

Now right-click the Sample cavalcade and select Remove.

Next, select the ColumnHeader column you lot created in a higher place and utilise Home>Transform>Group Past and configure the dialog similar this:

At present use Add together Column>General>Custom Column with this formula:

          Table.AddIndexColumn([GroupIndex], "Index", one, 1)

I chosen this new custom column DataWithGroupIndex.

At present I have three columns:

Correct-click GroupIndex and Remove that column, and so click the double-pointer in the tiptop correct hand corner of the DataWithGroupIndex cavalcade to expand the data that currently says "Table".

In the expand field dialog, I've configured it similar this:

I know this seems similar a lot of steps, but once done, this process volition be repeatable and you won't exist sat waiting for 7 hundreds of thousands of formulas to recalculate.

The point of the steps leading upward to here was to get an alphabetize column that repeats when the column header changes, which will exist important for the side by side pace.

Now select the ColumnHeader column and use Transform>Any Column>Pivot Cavalcade and configure it like this:

After clicking OK, you lot'll come across that the data are properly pinnacle-loaded into each Sample column.

You tin can right-click the Index and Remove information technology, then use Home>Close & Load to put the results back into the workbook.

If y'all want, you can merely open the attached workbook, select whatever cell in the dark-green table, go to the Query Tab, select Edit, and so on the Home Tab of the Power Query Editor, click Advanced Editor to encounter the code for the whole query, which you lot should be able to put into your own workbook with some minimal editing if you're comfortable with that.