# Transpose Problem Microsoft Microsoft excel 2004 (mac)
January 27, 2010 at 06:04:19
Specs: Macintosh OSX 10.6.2
 Hi.I have data on Sheet 1 which I need to transpose to Sheet 2...Current data:1 1 12 2 23 3 3 etc.Required result:111222 etc.I am using the following array function in cells A1:A3 on Sheet 2:{=transpose('Sheet1'A1:C1)}This works fine, except that when I copy it to the next three cells, the formula jumps by three rows instead of one, thus the results on Sheet 2:A1:A3 = 1, 1, 1 (correct)A4:A6 = 4, 4, 4 (wrong - should be 2, 2, 2)How can I adjust the formula to tell Excel to look at the second row instead of the fourth on Sheet 1? I am totally stumped! See More: Transpose Problem

#1 January 27, 2010 at 09:01:25
 If your data is really arranged as you say, then this formula will get you the results you want.If you have more than 3 columns, you'll need to make adjustments, but the concept works:=INDEX(Sheet1!\$A\$1:\$C\$3,COLUMN()+INT(ROW()/3.1),ROW()-INT(ROW()/3.1)*3)How it works:The INDEX function pulls values from an Array (Sheet1!\$A\$1:\$C\$3) by using a row_num and a col_num.For the row_num I'm using COLUMN()+INT(ROW()/3.1).Since the formula is in Column 1, this will evaluate to:1 + 0 for rows 1, 2 and 31 + 1 for Rows 4, 5 and 6 1 + 2 for Rows 7, 8 and 9Therefore your row_num values for the INDEX function will 1, 1, 1, 2, 2, 2, 3, 3, 3 for A1:A6The same concept is used for to calculate the col_num values for the INDEX function, except that I use the ROW number for the calculations.If you have more than 3 columns, just change the 3.1 to be 4.1 or 5.1 or whatever.Have fun!

Report •

#2
February 1, 2010 at 05:26:06
 Hi DerbyDad03, and thanks so much for your reply. Sadly, I don't think I can get it to work. You see, the numbers I gave were just for the purpose of giving a simple, clear example. But the actual data I want to transpose is from a list of photo URLs, and so is text and not numbers. Here's the actual layout of the data:Sheet 1Column A Column B Column Chttp://url/0001a.jpg http://url/0001b.jpg http://url/0001c.jpghttp://url/0002a.jpg http://url/0002b.jpg http://url/0002c.jpgAnd here's what I need it to look like on the second sheet:I'm guessing this makes things a whole lot more difficult...? :)

Report •

#3 February 1, 2010 at 05:58:31
 Hi,DerbyDad03's formula works fine.It doesn't matter what is in the cells in Sheet1.Regards

Report •

Related Solutions

#4 February 1, 2010 at 06:22:54
 Have you tried my suggested solution?The formula displays the contents of the cells in a single column (assuming you start the formula in A1) regardless of what is in the orginal cells. It could be a hard-coded value, the result of a formula or, yes, even a URL.I just tested it on your new example data and it works fine.If the problem is that you are not getting actual links after using the formula, try this: - Right click the sheet tab for the sheet with new list. - Paste the following code in the pane that opens. - Change to "9" to however many rows you have in the new list- Run the code to (hopefully) recreate the links.Disclaimer: I suggest you make a backup copy of your workbook in case something goes terribly wrong.```Option Explicit Sub Recreate_Links() Dim myRow, myLink For myRow = 1 To 9 myLink = Range("A" & myRow).Text ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & myRow), Address:= _ myLink, TextToDisplay:=myLink Next End Sub```

Report • 