Transpose columns into new column and maintain/duplicate rows accordingly

Question

Apologies for awkward title. I have searched through the forums but not yet come across a solution which is appropriate. I imagine what I am after will require the use of a macro.

I have a spreadsheet which outlines transactions by row, that is, one row for each transaction. Each transaction contains projected financial data. So, each row relates to one transaction which has financial impacts across multiple years.

For example, the header looks like

Transaction | field1 | field 2 | etc. | 2010-11 | 2011-12 | 2012-13 | etc. |

Beneath the years are dollar amounts.

All the fields contain data I need replicated across rows, except I want the year transposed into a single column, with the financials against it.

So if I have the record:

exampleid | data | data | 45000 | 56000 | 223145 | data

I would like it too become:

exampleid | data | data | 2010/11 | 45000  | data
exampleid | data | data | 2011/12 | 56000  | data
exampleid | data | data | 2012/13 | 223145 | data

There are around 30 columns.

Answer 1

You don't need VBA. You can use/adapt the formulas posted here. That post was intended precisely as a reference for cases like this one.

For each of your input rows, you need two sets of formulas row-to-column, one for the headers and one for the data.

Use appropriate relative/absolute indexes in formulas, to be able to copy/paste.

Answer 2

Here's what I use to do this. You can change the variables at the start of the sub to dictate how many columns you have with data, how wide your pivoted data is, which sheet names are used for the import and the export, etc etc.

The exported data will always start at the same column that the original data started at.

The data needs to have headers to work correctly, and it assumes there are no blank cells in the first column of data.

Sub pivot_data()

data_sheet = "Sheet1" 'name of sheet containing original data
export_sheet = "Sheet2" 'name of sheet to export to

start_d_row = 2     'first row of data (headings are grabbed from row above)
start_d_col = 1     'first column of data
end_d_col = 7       'last column of data
start_t_col = 4     'first column to pivot
end_t_col = 6       'last column to pivot
xrow = 2            'first row to export DATA to (headings will be row above)
trailing_col = end_d_col - end_t_col 'calculated number of trailing columns
pivot_size = end_t_col - start_t_col + 1 'calculated size of pivot

'headings

For xcol = start_d_col To start_t_col - 1
  Worksheets(export_sheet).Cells(xrow - 1, xcol).Formula = Worksheets(data_sheet).Cells(start_d_row - 1, xcol).Value
Next xcol
  Worksheets(export_sheet).Cells(xrow - 1, xcol).Formula = "Narrative"
  Worksheets(export_sheet).Cells(xrow - 1, xcol + 1).Formula = "Value"
For xcol = start_t_col + 2 To start_t_col + 2 + trailing_col - 1
  Worksheets(export_sheet).Cells(xrow - 1, xcol).Formula = Worksheets(data_sheet).Cells(start_d_row - 1, xcol - 2 + pivot_size).Value
Next xcol


'data

For irow = 2 To Worksheets(data_sheet).Cells(start_d_row - 1, start_d_col).End(xlDown).Row

  For icol = start_t_col To end_t_col

    For xcol = start_d_col To start_t_col - 1
      Worksheets(export_sheet).Cells(xrow, xcol).Formula = Worksheets(data_sheet).Cells(irow, xcol).Value
    Next xcol

      Worksheets(export_sheet).Cells(xrow, start_t_col).Formula = Worksheets(data_sheet).Cells(1, icol).Value
      Worksheets(export_sheet).Cells(xrow, start_t_col + 1).Formula = Worksheets(data_sheet).Cells(irow, icol).Value
    If end_d_col = end_t_col Then
    Else
      For xcol = start_t_col + 2 To start_t_col + 2 + trailing_col - 1
        Worksheets(export_sheet).Cells(xrow, xcol).Formula = Worksheets(data_sheet).Cells(irow, xcol - 2 + pivot_size).Value
      Next xcol
   End If
   xrow = xrow + 1
  Next icol

Next irow


End Sub

Hope that helps

Answer 3

VBA can certainly be used to solve this problem but for regularly structured data such as this native Excel alone can also be used.

The basic problem is that you want to split each record in your "input" list into n records in your "output" list. So if you have i records in your input list you will have i*n records in the output.

I used to do a lot this sort of stuff on the UK Office for National Statistics population data, where the data was published in neatly formatted tables showing population levels by age group and location whereas I wanted it in a list format with each record showing just the population in a single location and age group combination. The list format was often then used either with Excel's data filtering and/or Pivot tables for analysis purposes and occasionally imported into Access.

The approach to transforming the data is as follows.

1. Assuming for example you have 25 years worth of data in your input table and say 200 input records, you want to generate 200*25=5000 output records so first generate a 2 column list of with 5000 rows. Start with 1,1 in the 1st row; 1,2 in 2nd row; 1,3 in 3rd row; ... ;1,25 in 25th row; 2,1 in 26th row; 2,2 in 27th row; ... ; 2,25 in 50th row; 3,1 in 51st row; and so on until you reach 200,25 in the 5000th row. Such a list is easy enough to generate: assuming cells A2 and B2 represent the first 1,1 (simply type these values into the two cells) then B3 has a formula such as IF(B2=25,1,1+B2) which can be copied down the remaining rows of column B. A3 also contains a formula based on the IF function, but this time the first argument is based on the value in the adjacent cell (ie B3). The precise formula is left as an exercise and, again should be copied down the remaining rows of column A.
2. The 5000 rows correspond to your output records and each pair of values tells you which input record (first value) and which year within that record (second value) provides the data for the output record. You now have a choice of functions for using this information to create cells containing the output records. Possibilities include the INDEX(), OFFSET() and INDIRECT() functions though the latter will need to be used in conjunction with the ADDRESS() function.
3. INDEX() is probably most straightforward to understand and apply but the others are worth exploring and understanding. If your 200 input records, not including the header row, is held in say Sheet1!A2:AB201 (with cols A-C used for the non-year data and the 25 years of yearly data being in columns D-AB) and the output table is to appear on Sheet2 (with row 1 being a header row and columns A and B being used as described in step 1., above) then the formula for Sheet2!C2 would be =INDEX(Sheet1!A$2:A$201,$A2) and this formula can be copied both for all output rows and the next 2 (non-year) output columns (note the use of mixed absolute and relative addressing that achieves this). The year value for the 4th column of the output list (column F) is generated by picking out the correct value from the header row in Sheet1. The formula to use is =INDEX(Sheet1!$D$1:$AB$1,B2) in cell Sheet2!F2. Copy this formula down the remaining rows of column F. The formula for cell Sheet2!G2 uses the two dimensional version of the =INDEX() function - but I'll leave that one as an exercise. Once you've got the correct formula it should be copied down the remaining rows of column G.

If you have navigated the above successfully your output data should be in Sheet2!C2:G5001 and you can type your column headers into row 1.

One final comment on your terminology. You are not transposing your data, instead you are transforming the way it is presented. Transposing data usually means converting all the rows of a table into columns and vice-versa: so a table with I rows and J columns is transposed to become a table with J rows and I columns. In your case, the dollar values are transformed from I rows and J columns to I*J rows and 1 column.