transpose all data from columns to rows

Welcome to the forum.

This question was asked and responded during the late 30 days.
Please use the Search tool with the intricate keyword "transpose".



Yvan KOENIG (VALLAURIS, France) jeudi 29 avril 2010 18:20:59

jjdDFP wrote:
Thank you for the kind and quick reply, however, as best as I can tell, the solution you reference only transposes a single column or row, not an entire 22x12 (as in my case) spreadsheet to a 12x22 spreadsheet. If I do not have a correct understanding, please let me know. In fact, it is from that very reply that I figured the correct term to use for my question was "transpose".

You didn't read carefully.

I gave the reference to an archive available on my iDisk.
This one contain two scripts.
One transpose the 'dead' values of a multicolumns table.
The other one transpose 'living' values of a multicolumns table.

Yvan KOENIG (VALLAURIS, France) jeudi 29 avril 2010 19:32:07

The Numbers formula method would be as follows...

Assuming that the source table is "Table 1", and also assuming that the source table has no Header/Footer Rows/Columns, the following simple expression will do the transposition:

=IFERROR(INDEX(TRANSPOSE(Table 1), ROW(), COLUMN()), "")

Copy that expression and paste into the entire destination table. You will get whatever was being displayed in Table 1, transposed into the destination.

If you had formulas in Table 1, then you will need to convert the Table 1 cell formats to Text first so that the formulas will be brought over to the new table, but the relative addressing will not be adjusted as it would be in a conventional Move or Paste operation.

Then, as Yvan's script surely does, you will want to Copy/Paste Values to eliminate the transposition expressions from the destination table, leaving only the converted data.

The reason that the source table must not have headers or footers is that "TRANSPOSE(Table 1)" will only reference the body cells, not the Headers and Footers, throwing off the numbering among other things.

Jerry

If you are unable to read carefully and apply carefully the given instructions, I can't help you.



The script is designed to build a set of formulas buiding a transposed table.

step 1 - select the area to transpose, run the script.
step 2 - select the top_left cell of the transposed table, run the sript a second time.

The transposed area must be different (don't intersect) the source one..
And of course, we must keep both tables as the transposed one is grabbing the contents of the original table.

On the screenshot you may see, starting from the top :
the original range of cells
the living transposed range
the dead transposed range.

I just appied exactly what is explained in the instructions.

Yvan KOENIG (VALLAURIS, France) jeudi 29 avril 2010 21:01:48