How to Transpose a Row into a Column ? (URGENT)

HI Dancin' Brook,

I saw Sheet 1, which contained a table with data in row 1 and no other data.

I didn't see Sheet 2. My assumption is that its table contains the same data in column A, and no other data.

For that limited case, the analogous one with the original table containing data only in column A, and the extended case where the original table contains data only in row 1 and in column A, Table > Transpose returns what, for all appearances, is a correct result.

But Transpose affects the whole table, not just row 1 and column A. Consider, for example, the Source and Receiving tables below:

The 'data' in the block B2-J10 was created with a formula, then changed to fixed values by copying the cells then using Paste Formula Results to remove the formulas and leave the last calculated results.

Receiving is a copy of Source, pasted onto a sheet, then supplied the 'general' version of the OFFSET formula above, pasted into A1 and filled right to J1, and separately filled down to A10.

Here, the single row (1) and the single column (A) have been transposed. The rest of the data has been left undisturbed.

Without the omitted steps described above, Transpose won't do that.

Regards,

Barry

Dancin' Brook writes:

"Much easier now. There is now a menu item: Table>Transpose Rows and Columns"

Easier yes, but Table > Transpose Rows and Columns won't do what the OP asked for:

"I am trying to transpose a row into a column and please pay attention "Just one row of a table into a column of another table"." (emphasis added)

Regards,

Barry

Barry,

Copy-pasting the table, deleting rows one doesn't want to transpose, and applying Table > Transpose Rows and Columns IS much easier now than having to come up with an OFFSET that works and having to clean up afterwards.

Here it takes less than a minute, far less than the time it takes to put in the formulas. One row to one column in another table.

So why do you think Dancin' Brook is wrong? Is your testing there different?

SG

Hi SG,

Not 'wrong,' just incomplete. Table > Transpose does the whole table, not a 'single row.'

Had Dancin' Brook provided the omitted details of copy, paste, delete rows that are not to be transposed, go Table > Transpose, then do whatever needs doing to restore the deleted rows, it would be a fine answer to the original question.

Wayne's response answered the question that was asked with one of the techniques useable at the time. My response to the follow up question addressed that question. Neither response would disturb any existing data except that in the row and column to be transposed.

Regards,

Barry

PS to Dancin' Brook: Will reply later. Time sensitive chores to do now.

B

Not sure your reason or circumstances but seems simple enough to use a 2nd sheet, or even a 3rd if needed for a simple transposition, but it it doesn't work given your circumstance then it doesn't. Perhaps I'll demo again with your sample. BTW, perhaps best to share actual files so others can download and see formulas too.

Receiving is a copy of Source. It may be place on the same sheet as Source, or on a separate sheet as desired, with no change to the formula used.

The formula used in all cells in row 1 and in all cells in column A of Receiving is the one labeled "the more general formula, which will tanspose in either direction" in my June 13, 2011 post:

=OFFSET(Source :: $A$1,COLUMN()-1,ROW()-1,,)

Regards,

Barry

You can use the OFFSET() function to perform the transpose function. I am certain very similar posts have covered this topic so I am gently reminding you to place value on peoples time who help out here by reviewing similar posts.

=OFFSET(Source :: $1:$1,0,ROW()-1,1,1)

I hope this helps your urgent cause.

Wayne

Essentially the same thing.

In COLUMN B: =OFFSET(Source :: $A$1,1,ROW()-1,,)

In ROW 2: =OFFSET(Source :: $A$1,COLUMN()-1,1,,)

B2 may contain either formula.

OR use the more general formula, which will tanspose in either direction:

=OFFSET(Source :: $A$1,COLUMN()-1,ROW()-1,,)

Regards,

Barry

Much easier now. There is now a menu item: Table>Transpose Rows and Columns

Is this what you mean?

Two sheets, same data, one in rows and one in columns. Copy and paste and transpose.

Or maybe explain more about what you mean.