Sorting Columns and keep information in Rows intact

I am trying to sort/filter the information by columns but I need the information in each row to stay together for instance...

1 2 3 4 5
2 3 4 5 1
3 4 5 1 2
4 5 1 2 3
5 1 2 3 4

I need to sort the numbers in column 3 in ascending order or filter them but i need the entire row to move when sorted so it needs to look like this


4 5 1 2 3
5 1 2 3 4
1 2 3 4 5
2 3 4 5 1
3 4 5 1 2

I am using the list function to sort and filter the data in the columns but when i try to sort them in ascending order i get....

1 2 1 4 5
2 3 2 5 1
3 4 3 1 2
4 5 4 2 3
5 1 5 3 4

I don't know how to write formulas or VBA's or anything but am willing to try.

Is it possible to sort the data and get a result like the second set of data? and is there an easy way to make this fool proof so people that use the spreadsheet will not mess up the data?

Hi,

Welcome to the forum, select your whole data range A1:E5, go to Data > Sort, click no header row and sort by column C ascending

If you are using the list function already , then select the arrow drop down in the column you wish to sort and choose >>Sort Ascending.

I tried with your test data and it worked fine...

... can you post a sample workbook so we can see if there is something else not right

Well, the problem is you've got 10 independent lists there...

You need to make all columns into 1 single list..

So right-click each column and go to List >> Convert to Range.

Then select the entire range and go to Data|List|Create List...

Now you should be able to sort as desired...

This is what I have done in Excel 2010, after highlighting all columns:

From the HOME tab - click on "Sort & Filter".

Then chose "custom sort" and you will get a window to manipulate your data sheet.

The first drop down is the column you want to sort keeping the other columns with their respective data to follow the sorting results. Keep "Sort on Values" the same and the last drop down is the order in which you want your chosen column to sort. All other columns will realign their data to stick with your chosen column resorted data. I hope that made sense.

The title should read more like: Sorting Columns while all other column's data follows that of the sorted column