Transposing Excel Columns

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

hip48

Thread Starter
Joined
Jun 10, 2005
Messages
9
If you have Excel data on columns A,B and C sorted by column A, is there a simple way of transposing the columns so you can view the columns in the order of B,C,A sorted by column B, without rearranging the columns by dragging them to new locations?
 

WhitPhil

Gone but never forgotten
Trusted Advisor
Joined
Oct 4, 2000
Messages
8,684
Ensure that your worksheet is saved first.

Then,select the rows/columns that contain you data then

Data > Sort
select col B as sort

Note that when you exit it will ask if you wish to save, since you have now resorted the data. Answer No, unless you really want it saved that way.

Or, were you really asking how to sort by B AND have it on the left side of column C?
 
Joined
Feb 20, 2006
Messages
2,255
To sort your columns i.e.
make Column B move to Column A
make Column C move to Column B
make Column A move to Column C

you need field headings in row one that will allow you to sort by column.

So lets say your field headings in row1 are

C | A | B

Make sure you are in one of the cells of your data range

Then select Data, Sort

Then click on Options and select Orientation, Sort Left to Right and then click on OK

Then in the first sort by option, click on the drop down icon and select row1 and then click on OK

Your columns of data will be moved across based on the alpha basis in Row !

Bear in mind that all the data in column B in the example will finsih up in Column A, Data in Column C will finish up in Column B and data in Column A will finish up in Column C

Hope that helps.
 

WhitPhil

Gone but never forgotten
Trusted Advisor
Joined
Oct 4, 2000
Messages
8,684
Villian

Your solution doesn't sort by columns, it sorts by rows. All of the data will no longer be column based since it will be moved based on the sort. That is what the orientation change does. Leaving it as default will sort by columns.

And, you don't need Field Headings, you just Select "No Header Row".
 
Joined
Feb 20, 2006
Messages
2,255
WhitPhil

What I meant by that was if you sort left to right on the following example

3 2 1
2 3 1

The result will be

1 2 3
1 3 2

You cant select No Header row in that situation.

The reason I said C A B
is that you have to sort by a row in this case row one
It will then use the letter in A1 B1 & C1

and take the data in column B and shift it into column A
and take the data in column C and shift it into column B
and take the data in column A and shift it into column C

So I am not clear what you are on about
 

WhitPhil

Gone but never forgotten
Trusted Advisor
Joined
Oct 4, 2000
Messages
8,684
Villan:

Thanks. Let me clarify where I'm coming from:

If I the OP's post correctly, they have data as follows, sorted by column A

Joe Technician Level 1
Ralph Senior Tech Level 5
Tom Junior Tech Level 8

That is, the data is in sequence by name.

They then want that sorted by Column B so that it results in

Tom Junior Tech Level 8
Ralph Senior Tech Level 5
Joe Technician Level 1

IE: Sorted by Title

They also indicated they wanted Column B first but they haven't responded. A simple column cut, insert before the sort would accomodate this.

If you were to sort this data left to right using Row 1 it would end up as

Joe Level 1 Technician
Ralph Level 5 Senior Tech
Tom Level 8 Junior Tech

Because Joe is already the lowest sort order in row 1, then Level then Technician

If you select the data and sort by row 2 it results in

Level 1 Joe Technician
Level 5 Ralph Senior Tech
Level 8 Tom Junior Tech

because Level 5 is lower than Ralph and Senior so that column comes first.

If you sort by row 3 the result is

Technician Level 1 Joe
Senior Tech Level 5 Ralph
Junior Tech Level 8 Tom

because Junior is before Level which is before Tom
 

WhitPhil

Gone but never forgotten
Trusted Advisor
Joined
Oct 4, 2000
Messages
8,684
The Villan said:
Best to wait WhitPhil :)
Agreed. But while we do, is that what you thought a Left to Right sort was going to do?

I've never used that and, after testing it, am not sure when I would use it. I saw a post somewhere that wanted to sort their column headers, and I can see using it then.
 
Joined
Jul 25, 2004
Messages
5,458
.. to interject MHO.. (LOL!) I think it would be just select columns A:C | Data | Sort | Sort by B | Ok, select column A, right click the column header, select Cut, right click column C header, select Insert Cut Cells.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top