Transpose columns to rows as a sub-group

Microsoft Excel 2007
September 24, 2009 at 19:44:13
Specs: Windows Vista

I have the following data:

Row 1 __ R1 R2 R3 R4
Row 2 P1 20 __ 20 __
Row 3 P2 __ 20 10 20
Row 4 P3 10 10 10 __
Row 5 P4 __ __ 30 __

I would like to transpose it to:

Row 1 P1 R1 20
Row 2 P1 R2
Row 3 P1 R3 20
Row 4 P1 R4
Row 5 P2 R1
Row 6 P2 R2 20
Row 7 P2 R3 10
Row 8 P2 R4 20
Row 9 P3 R1 10
Row 10 P3 R2 10
Row 11 P3 R3 10
Row 12 P3 R4
Row 13 P4 R1
Row 14 P4 R2
Row 15 P4 R3 30
Row 16 P4 R4

Can you please help on how I could do it in a way that the transposed cells still refer back to original cell values (so when I change the values in the original cell, it's automatically reflected in the transposed cells as well)?

My actual data has over 50 rows and 50 columns. When this is fully exploded out like above, I should end up with over 2500 rows.

Appreciate a response.

See More: Transpose columns to rows as a sub-group

Report •

September 25, 2009 at 04:47:31

Two things from your example:
1. the number of items in the original data is different to the number of items in th transposed data.
Because of this, I can't work out the relationship between the original and the transposed data. Can you give an example that shows transposed data, based on identical original data.
I am assuming that the 'transposition' is 1:1

2. What are the underscore characters in the original data.
Are they meant to represent blank/empty cells?
If so how are these empty cells handled in the transposition, as there were no 'underscores' in your sample of transposed data.

I am not sure if you used the

 tags for displaying your data.  They make formatting data into columns a lot easier.


Report •

September 25, 2009 at 06:07:40
I think I figured out the pattern!

All Px's will have an Ry associated with it.

P1 R1
P1 R2

Then we look down below the R heading and pull the value at the intersection of a Px.


P1, R2 has no value at its intersection.
P3, R1 has a 10 at its intersection

If that's correct, I would use VLOOKUP and MATCH.

However you want, set up your table so that you have
P1 R1
P1 R2
P1 R3

There are ways to do this manually as well as automatically; it really depends on your actual data.

So let's say the original table is in A1:E5 and the transposed list will begin in A8 (A8 = P1, B8 = R1, etc).

In C8 I would use this:


The VLOOKUP will find the Px values in Column 1 of A1:E5 and the MATCH function will find the column number of the Rx values from A1:E1, which VLOOKUP will use as the col_index_num.

Report •

September 25, 2009 at 07:08:07
Hi DerbyDad03,

Yes, now you have worked it out, its obvious !!!!


Report •

Related Solutions

September 25, 2009 at 09:42:45
I am sorry, I did not make this clear. Actually, what I am trying to accomplish is to automate the creation of the 'cartesian product' of rows and columns. Once that's done, I can use the method you listed above to find the corresponding value for the combination (the actual numbers in this example). To be clear, I am looking to automate the 'explosion' of a matrix of x rows and y columns to x.y set of rows (as in P1-R1, P1-R2, P1-R3, P1-R4, P2-R1, P2-R2, etc). Hope this is clear.

Report •

September 25, 2009 at 13:59:57
re: I did not make this clear

That's for sure!

What you want could easily be done with some VBA code, but I don't have access to Excel (only OOO3) for the next few days, so I can't offer any code.

However, these 2 formulae, if placed in Row 1 and dragged down should give you:

P1 R1
P1 R2
P1 R50
P2 R1
P2 R2
P2 R50
P3 R1

For the P's:


For the R's:


If you are not starting in Row 1 or have something other than 50 pairs of x-y data, you'll need to make adjustments.

Report •

September 26, 2009 at 05:59:26

If you need to create different sized matrices such P1 to P100 with R1 to R20
the following code will do that.
It creates the original table, then below the table it creates three columns
First column is R1 etc
Second column is P1 etc
Third column is the formula DerbyDad03 gave you

For this code to work you need the input parameters in a fixed area of the worksheet as follows:

	B	C	D
2	P	Start	Finish
3		1	100
5	R	Start	Finish
6		1	20
8	Start 	E11
	for matrix

This allows you to specify the size and range of the matrix and the address of the cell where the matrix starts
The start cell is just input as text in cell C8

In this example the matrix starts at E11 and the last cell is DA31
I put x and y in cells F12 and F13

The first two lines of output are as follows:

	E	F	G
34	R1	P1	x
35	R2	P1	y

Put this code in a standard module

Option Explicit

Sub explarray()
Dim intPstart As Integer
Dim intPfinish As Integer
Dim dblRstart As Double
Dim dblRfinish As Double
Dim rngStartCell As Range
Dim strStartCell As String
Dim dblStartRow As Double
Dim strFormula As String
Dim rngFmlaFirst As Range
Dim rngFmlaLast As Range
Dim rngMatchFirst As Range
Dim rngMatchLast As Range
Dim c As Integer
Dim r As Double

'get the parameters
With ActiveSheet
    intPstart = .Range("C3").Value
    intPfinish = .Range("D3").Value
    dblRstart = .Range("C6").Value
    dblRfinish = .Range("D6").Value
    strStartCell = .Range("C8").Text
    Set rngStartCell = Range(strStartCell)
End With

'create matrix
For c = intPstart To intPfinish
    rngStartCell.Offset(0, c).Value = "P" & Format(c, "##0")
Next c
For r = dblRstart To dblRfinish
    rngStartCell.Offset(r, 0).Value = "R" & Format(r, "##0")
Next r

'calculate some addresses mainly for the VLOOKUP function
dblStartRow = CDbl(rngStartCell.Row) + dblRfinish - dblRstart + 3
Set rngFmlaFirst = rngStartCell.Offset(1, 0)
Set rngFmlaLast = rngStartCell.Offset(dblRfinish, intPfinish - intPstart + 1)
Set rngMatchFirst = rngStartCell.Offset(0, 1)
Set rngMatchLast = rngStartCell.Offset(0, intPfinish - intPstart + 1)

'create first formula (this is all one line)
strFormula = "=VLOOKUP(" & CStr(rngStartCell.Offset(dblRfinish - dblRstart + 4, 0).Address(False, True)) & _
"," & CStr(rngFmlaFirst.Address) & ":" & CStr(rngFmlaLast.Address) & _
",MATCH(" & CStr(rngStartCell.Offset(dblRfinish - dblRstart + 4, 1).Address(False, True)) & _
"," & CStr(rngMatchFirst.Address) & ":" & CStr(rngMatchLast.Address) & ",0)+1,0)"

'start cell - one below matrix, same column
Set rngStartCell = Cells(dblStartRow, rngStartCell.Column)

'paste first formula
rngStartCell.Offset(1, 2).Formula = strFormula
'copy formula ready to paste it in cell following each pair of references
rngStartCell.Offset(1, 2).Copy

'create two columns of references (R1; P1) followed by the formula
For c = intPstart To intPfinish
    For r = dblRstart To dblRfinish
        rngStartCell.Offset(r + (dblRfinish * (c - 1)), 0).Value = "R" & Format(r, "##0")
        rngStartCell.Offset(r + (dblRfinish * (c - 1)), 1).Value = "P" & Format(c, "##0")
        rngStartCell.Offset(r + (dblRfinish * (c - 1)), 2).PasteSpecial (xlPasteFormulas)
    Next r
Next c

End Sub


Report •

September 27, 2009 at 00:31:01
Brilliant, that worked beautifully. Thanks a million.

Just an extension to my question.

The example above is for a project portfolio resource plan, where P's stand for Project names and R's stand for resource names, with the numbers for a given project and resource stand for the estimated numbers of days assigned/allocated to that resource for the respective project for a given quarter in the year. I also have for each of the projects a start and end date for major phases of the project. I would like to build a weekly plan for the quarter by breaking out the total quarterly resource estimate down to weekly levels based on the project timelines which are maintained in each row (next to all the R values).

E.g., Let's say I have a project P1, requiring a resource R1 for a total of 10 days in Q4, with the project development starting on 10/1/09, ending on 10/31/09, QA starting on 11/1/09 and ending on 11/15, and release and support starting on 11/16 and ending on 11/20. Also let's say, I use the rule of thumb that 60% of a developer timeis spent during development phase, 30% during QA phase and 10% during release & support phase. With this rule of thumb, I would now like to use the above exploded rowsets, and adding additional columns from Week 1 to Week 13 for the quarter, and allocate the 10 days as -> 6 days (60% of total) during the dev phase split over the 4+some weeks of dev (10/1 - 10//30), 3 days (30% of total) during QA phase (11/1 to 11/15) and 1 day over the release phase (11/16 - 11/20).

Once I Have this allocated this way, I would like to be able to see how resource leveling is over the quarter, to see if there are any bottlenecks or such.

Sorry, I had to write quite a bit to explain what I am trying to accomplish. I hope this is somewhat clear.

Is there an easy way to automate this?

Thanks in advance!!!!

Report •

September 27, 2009 at 04:51:38

What you are asking for is a fairly significant piece of programming which I could not do.

I suggest that you look at Microsoft Project, a program designed for project management and resource allocation.


Report •

September 28, 2009 at 08:50:28
re: Brilliant, that worked beautifully. Thanks a million.

You received two suggestions, one formula based, one VBA based.

It would help those following the thread, as well as the responders, if you indicated which of the suggestions worked beautifully.

Since you added an additional request, it's hard for us to tell which of the 2 suggestions you would like modified to fulfill your new requirements.

Report •

September 28, 2009 at 09:26:35
I tried the one that was VBA based. I am sure I could get the formula-based suggestion to work as well. The reason I went with the VBA-based approach was that I needed a bit more control. For example, If a particular combination did not have a value, which is likely the case with most resources as only a handful of them are assigned to any given project, I would like to be able to skip those entirely. This will llimit the number of rows to a minimum. Typically, I have about 50 project and 50 resources, with an average of about 5 resources assigned to a project. With this approach, I only have to deal with 250 rows (50 x 5) and not 2,500 rows (50 x 50). I could do this with VBA better than I can with the formula, even if I can with the latter.

Report •

Ask Question