Transpose Problem

Microsoft Microsoft excel 2004 (mac)
January 27, 2010 at 06:04:19
Specs: Macintosh OSX 10.6.2

I have data on Sheet 1 which I need to transpose to Sheet 2...

Current data:

1 1 1
2 2 2
3 3 3 etc.

Required result:

2 etc.

I am using the following array function in cells A1:A3 on Sheet 2:

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

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:


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 3

1 + 1 for Rows 4, 5 and 6

1 + 2 for Rows 7, 8 and 9

Therefore your row_num values for the INDEX function will 1, 1, 1, 2, 2, 2, 3, 3, 3 for A1:A6

The 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 •

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 1
Column A Column B Column C
http://url/0001a.jpg http://url/0001b.jpg http://url/0001c.jpg
http://url/0002a.jpg http://url/0002b.jpg http://url/0002c.jpg

And here's what I need it to look like on the second sheet:

Sheet 2
Column A

I'm guessing this makes things a whole lot more difficult...? :)

Report •

February 1, 2010 at 05:58:31

DerbyDad03's formula works fine.

It doesn't matter what is in the cells in Sheet1.


Report •

Related Solutions

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
End Sub

Report •

Ask Question