How do I reshape date in Excel copying some of the columns?

I have a table that has 5 columns like this:

A1 B1 C1 D1 E1
A2 B2 C2 D2 E2
A3 B3 C3 D3 E3
...

and I need it to reshape it into a 3 column table like this:

A1 B1 C1
A1 B1 D1
A1 B1 E1
A2 B2 C2
A2 B2 D2
A2 B2 E2
A3 B3 C3
A3 B3 D3
A3 B3 E3
...

What's the easiest way to do it in Excel 2016? TIA!

4 Answers

In F1 enter:

=INDEX(A:A,ROUNDUP(ROWS($1:1)/3,0))

In G1 enter:

=INDEX(B:B,ROUNDUP(ROWS($1:1)/3,0))

In H1 enter:

=OFFSET($C$1,ROUNDUP(ROWS($1:1)/3,0)-1,MOD(ROWS($1:1)-1,3))

and copy these downward:

The easiest way to transpose data from columns to rows is as follows:

1. Select and copy the data that you would like to change from columns to rows
2. Right click on a new location on the excel data sheet
3. Select 'Paste Special' then choose to paste 'Transpose (T)'

Source

If you have lots of data, you could consider PowerQuery for this to avoid lots of formulas.

Click anywhere in your data and use Data>Get & Transform Data>From Table/Range:

In the Power Query Editor, select the columns you want to un-pivot and use Transform>Any Column>Unpivot Only Selected Columns (use the "Unpivot Columns" drop-down list):

After the unpivot, right click the "Attribute" column and choose "Remove":

Now use Home>Close & Load to put the results into a worksheet in your workbook.

One of the benefits of this method (apart from being fast and easy to understand) is that if you get more data added to your source table, you can re-run the unpivot by right-clicking the output and choosing "Refresh" from the context menu.

What you are trying to do is to Unpivot or Melt. There are three ways I can think of to do this:

• Create an OFFSET formula as Gary did.
• Use PowerQuery within the Power BI engine as Flex did.
• Create a dynamic array, which I will show below.

Each has pros and cons. The Offset has the advantage of giving an instantaneous answer as soon as you enter data. On the down side, you have to copy the formula to the destination, so if the size of your input data changes, you have to copy the formula accordingly. Also, OFFSET is a volatile function, which may or may not impact performance on your workbook.

The Power Query method requires you to Refresh Data each time the data are changed.

Dynamic Arrays are like offset in that they calculate immediately, but they have the advantage of reshaping themselves as the data change and they are non-volatile. They have a disadvantage in that they will dynamically write the results into the worksheet themselves, so if you have any occupied cells in the way, the formula will SPILL.

That said, here is a Dynamic Array approach using LET to make it easier to understand, modify and debug.

=LET( unPivMatrix, A1:B3, byMatrix, C1:E3, upC, COLUMNS( unPivMatrix ), upCells, MIN( ROWS( unPivMatrix ), ROWS( byMatrix ) ) * upC, upSeq, SEQUENCE( upCells,, 0 ), byC, COLUMNS( byMatrix ), mux, INDEX( unPivMatrix, upSeq/upC + 1, MOD( upSeq, upC ) + 1 ), demux, IFERROR( INDEX( byMatrix, IFERROR( INT( SEQUENCE( upCells, byC,0 )/byC/upC ) + 1, MOD( upSeq, upC ) + 1 ), SEQUENCE( 1, byC + 1 ) ), mux ), demux )

The unPivMatrix are the columns you want to distribute - so these would be the reviewers' identities in your example. The byMatrix are the columns you want to unpivot them by - these would be the SKU components in your example. Think about a Pivot Table in excel. The byMatrix are the rows that you are pivoting the data by and the unPivMatrix are the data that you want to unpivot.

This formula first creates a shape for the unpivot, so that it can be multiplexed into a single column (called mux) like this:

The result is put into demux which first creates an array of the byMatrix that repeats the byMatrix column values for each column in the unPivMatrix

By referencing one more column than what actually exists in byMatrix, the INDEX function throws an error.

This error is exploited to effectively fuse the two arrays (demux of byMatrix and mux) together. By wrapping the result in an IFERROR, we are able to replace the #REF! error with the values from mux.

This formula is now a general purpose unpivot based on dynamic arrays. i.e. you can put any range into unPivMatrix and byMatrix and get the result you want in much the way that UNPIVOT works in Power Query.