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

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: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. 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 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 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 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 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 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 4 5 R Start Finish 6 1 20 7 8 Start E11 cell 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 Regards