Microsoft Excel 2007

Hi, 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 R4Can 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.

Hi, 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:12. 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.Regards

I think I figured out the pattern! All Px's will have an Ry associated with it.

P1 R1

P1 R2

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

e.g.

P1, R2 has no value at its intersection.

P3, R1 has a 10 at its intersectionIf 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

etc.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:

=VLOOKUP($A8,$A$2:$E$5,MATCH($B8,$A$1:$E$1,0),0)

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.

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

Regards

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.

re: I did not make this clearThat'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

etc.For the P's:

="P"&IF(MOD(ROW(),50)=0,ROW()/50,ROUNDDOWN(ROW()/50,0)+1)For the R's:

="R"&IF(MOD(ROW(),50)=0,50,MOD(ROW(),50))

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.

Hi, 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 youFor 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 4 5 R Start Finish 6 1 20 7 8 Start E11 cell for matrixThis 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 C8In this example the matrix starts at E11 and the last cell is DA31

I put x and y in cells F12 and F13The first two lines of output are as follows:

E F G 34 R1 P1 x 35 R2 P1 yPut 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 SubRegards

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!!!!

Hi, 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.

Regards

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

whichof the suggestionsworked 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.

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.

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History