reversing spreadsheet data
- Thread starter guitarman
- Start date
NARAYANK991
Excel Ninja
Hi Mike ,
In column H ( I assume this is an unused column ) , enter the formula =ROW() , and copy it down till row 1250 or wherever your data extends.
Now SORT your data ( column A through H ) using column H as the sort column , sorting from Largest to Smallest. Your data will be reversed.
Narayan
In column H ( I assume this is an unused column ) , enter the formula =ROW() , and copy it down till row 1250 or wherever your data extends.
Now SORT your data ( column A through H ) using column H as the sort column , sorting from Largest to Smallest. Your data will be reversed.
Narayan
Hi Narayan
I trust you are well.Thanks for the info but I dont want to sort the data I want to flip it so the data in A1250 appears in A1.
A-B-C-D-E- ------------A-B-C-D-E
1-2-3-4-5 ------------11-12-13-14-15
6-7-8-9-10 -------------6-7-8-9-10
11-12-13-14-15----------1-2-3-4-5.
So as you can see the data is just reversed from the bottom row to the top row and visa versa.
Mike
I trust you are well.Thanks for the info but I dont want to sort the data I want to flip it so the data in A1250 appears in A1.
A-B-C-D-E- ------------A-B-C-D-E
1-2-3-4-5 ------------11-12-13-14-15
6-7-8-9-10 -------------6-7-8-9-10
11-12-13-14-15----------1-2-3-4-5.
So as you can see the data is just reversed from the bottom row to the top row and visa versa.
Mike
SirJB7
Excel Rōnin
Hi, guitarman!
The formula with "INDICE" is my original formula as I use an Spanish Excel version, so the right for you is that with "INDEX".
It'2 working, I'm looking at it again, it should work for you, unless you don't have all the 1250 rows filled with data.
If so, could you please upload the file so as to check it?
Regards!
The formula with "INDICE" is my original formula as I use an Spanish Excel version, so the right for you is that with "INDEX".
It'2 working, I'm looking at it again, it should work for you, unless you don't have all the 1250 rows filled with data.
If so, could you please upload the file so as to check it?
Regards!
NARAYANK991
Excel Ninja
Hi Mike ,
The SORT is just a facility you are using ! When you enter the formula =ROW() in a particular column , you are just filling the column with numbers from 1 through 1250 ; now when you sort your data using this column , from Largest to Smallest , since 1250 is the largest value in the column , that row of data will come to the top , then the row containing 1249 , and so on.
Essentially your data will be flipped vertically.
This will work assuming your database consists of only data , without any formulae. Try it and see.
Narayan
The SORT is just a facility you are using ! When you enter the formula =ROW() in a particular column , you are just filling the column with numbers from 1 through 1250 ; now when you sort your data using this column , from Largest to Smallest , since 1250 is the largest value in the column , that row of data will come to the top , then the row containing 1249 , and so on.
Essentially your data will be flipped vertically.
This will work assuming your database consists of only data , without any formulae. Try it and see.
Narayan
NARAYANK991
Excel Ninja
Hi Mike ,
=ROW() is correct ; but you say that in row 1 , you have 1 , in row 2 you have 2 and so on.
If in H1250 you have 1250 , and then you sort using column H as the sort order column , then your data should be flipped.
Why don't you try using some random data in about 10 cells on a new worksheet ?
Narayan
=ROW() is correct ; but you say that in row 1 , you have 1 , in row 2 you have 2 and so on.
If in H1250 you have 1250 , and then you sort using column H as the sort order column , then your data should be flipped.
Why don't you try using some random data in about 10 cells on a new worksheet ?
Narayan
NARAYANK991
Excel Ninja
Hi Mike ,
Nice to know your problem has been resolved. Thanks for the feedback. Have a great day.
Narayan
Nice to know your problem has been resolved. Thanks for the feedback. Have a great day.
Narayan
Hi there Narayan
Its me again can you help me out with this sorting of rows of numbers. I have approx 850 rows of numbers that have been created with the formulae we did previously but they have not come out in the right order so i want to sort them from small to large. I have done the sort with them one at a time but I want to do the whole block (850 rows)in one go.I have tried by selecting the whole lot and setting left to right sort. I then click sort and all it does is (1 row) I would have thought it would do all 850. Does excel do this or is there something you have to do with the selection?
Mike
Its me again can you help me out with this sorting of rows of numbers. I have approx 850 rows of numbers that have been created with the formulae we did previously but they have not come out in the right order so i want to sort them from small to large. I have done the sort with them one at a time but I want to do the whole block (850 rows)in one go.I have tried by selecting the whole lot and setting left to right sort. I then click sort and all it does is (1 row) I would have thought it would do all 850. Does excel do this or is there something you have to do with the selection?
Mike
NARAYANK991
Excel Ninja
Hi Mike ,
I have not understood your problem ; you say you have 850 rows of numbers ; you want to sort these from smallest to largest.
Is your data over several columns or only one column ?
When you select the SORT facility , have all the options been set correctly ? I think if you have done a sort earlier , the same options are retained. Can you check each of the options and see whether they are right for this sort ?
Narayan
I have not understood your problem ; you say you have 850 rows of numbers ; you want to sort these from smallest to largest.
Is your data over several columns or only one column ?
When you select the SORT facility , have all the options been set correctly ? I think if you have done a sort earlier , the same options are retained. Can you check each of the options and see whether they are right for this sort ?
Narayan
Hi Narayan
The data is across (6) columns A,B,C,D,E,F and the layout is like this
A-B-C-D-E-F
4-6-2-5-8-3
7-1-3-6-2-2
9-6-8-4-7-1
and they should be like this
A-B-C-D-E-F
2-3-4-5-6-8
1-2-2-3-6-7
1-4-6-7-8-9
So when I highlight all the data and ask it to sort from left to right it just sorts one (1) line the top one and I have checked everything and it is set with all the rows listed but it only does one at a time.
Mike
The data is across (6) columns A,B,C,D,E,F and the layout is like this
A-B-C-D-E-F
4-6-2-5-8-3
7-1-3-6-2-2
9-6-8-4-7-1
and they should be like this
A-B-C-D-E-F
2-3-4-5-6-8
1-2-2-3-6-7
1-4-6-7-8-9
So when I highlight all the data and ask it to sort from left to right it just sorts one (1) line the top one and I have checked everything and it is set with all the rows listed but it only does one at a time.
Mike
NARAYANK991
Excel Ninja
Hi Mike ,
Sorry , but that is not how a SORT will work.
The SORT will go by the levels of sorting ; when you have selected sorting by rows instead of columns , the first row will be sorted ; the second level is used only when there are two items with the same value in the first level ; the same goes for the third level.
In your case , the first row is sorted correctly as follows :
2-3-4-5-6-8
When it comes to the second row , this cannot be rearranged in order , since the column order of the data has to be preserved ; the data in the second row will have to correspond with data in the first row ; in your original data , under 2 you had 3 , under 3 you had 2 , under 4 you had 7 and so on ; this order will be preserved. The same goes for the third row. Hence the final result that you get as follows , is correct :
[pre]
[/pre]
If you want the result that you are looking for , then you will have to sort your data , one row at a time.
Narayan
Sorry , but that is not how a SORT will work.
The SORT will go by the levels of sorting ; when you have selected sorting by rows instead of columns , the first row will be sorted ; the second level is used only when there are two items with the same value in the first level ; the same goes for the third level.
In your case , the first row is sorted correctly as follows :
2-3-4-5-6-8
When it comes to the second row , this cannot be rearranged in order , since the column order of the data has to be preserved ; the data in the second row will have to correspond with data in the first row ; in your original data , under 2 you had 3 , under 3 you had 2 , under 4 you had 7 and so on ; this order will be preserved. The same goes for the third row. Hence the final result that you get as follows , is correct :
[pre]
Code:
2 3 4 5 6 8
3 2 7 6 1 2
8 1 9 4 6 7If you want the result that you are looking for , then you will have to sort your data , one row at a time.
Narayan
Hi Narayan
Many thanks for your info and knowledge. I was not aware Excel sort worked like that.I would suggest Microsoft revamp this sort programme because it is not much use like that only one (1) row at a time blooming crazy I will be here forever doing 850 rows.But anyway if that is how it is then that is how it is.Many thanks for your time and trouble and have a great day and a brilliant life.
Mike
Many thanks for your info and knowledge. I was not aware Excel sort worked like that.I would suggest Microsoft revamp this sort programme because it is not much use like that only one (1) row at a time blooming crazy I will be here forever doing 850 rows.But anyway if that is how it is then that is how it is.Many thanks for your time and trouble and have a great day and a brilliant life.
Mike
NARAYANK991
Excel Ninja
Hi Mike ,
If you really want the job done , a macro can do it. Are you looking for one ?
Narayan
If you really want the job done , a macro can do it. Are you looking for one ?
Narayan
NARAYANK991
Excel Ninja
Hi Mike ,
If it is not urgent , I ( or anyone else ) will have it for you by tomorrow.
Narayan
If it is not urgent , I ( or anyone else ) will have it for you by tomorrow.
Narayan
NARAYANK991
Excel Ninja
Hi Mike ,
Try out this code , but make a copy of your worksheet before you execute this , just in case ! Please note that I have defined a named range "Data_Range" which encompasses the entire data range ; before you run this procedure , you will have to define this range for your data.
[pre]
[/pre]
Copy this procedure in the section for the worksheet which has the data.
Narayan
Try out this code , but make a copy of your worksheet before you execute this , just in case ! Please note that I have defined a named range "Data_Range" which encompasses the entire data range ; before you run this procedure , you will have to define this range for your data.
[pre]
Code:
Public Sub Sort_Entire_Range()
Set Sort_Range = Range("Data_Range")
For Each row_num In Sort_Range.Rows
row_num.Sort Key1:=row_num, Orientation:=xlSortRows
Next
End SubCopy this procedure in the section for the worksheet which has the data.
Narayan
Hi Narayan
Thanks for that I will try to work out how to use it because I have never used Macros before I assume you just enter it into a module in VBA is that right or does it go straight onto the worksheet? Anyway I have to go now got to play nine holes before it gets dark.See you tomorrow Many Thanks
Mike
Thanks for that I will try to work out how to use it because I have never used Macros before I assume you just enter it into a module in VBA is that right or does it go straight onto the worksheet? Anyway I have to go now got to play nine holes before it gets dark.See you tomorrow Many Thanks
Mike
NARAYANK991
Excel Ninja
Hi Mike ,
Press ALT and the F11 key together to bring up the VBA Project Window. On the left side of your window , you will see the Project Explorer ; double click on the Sheet name where you want to insert this procedure. Copy and paste this procedure on the right side within the blank window.
Define the name "Data_Range" ( without the quotes ! ) to encompass your data range , and run the procedure.
Narayan
Press ALT and the F11 key together to bring up the VBA Project Window. On the left side of your window , you will see the Project Explorer ; double click on the Sheet name where you want to insert this procedure. Copy and paste this procedure on the right side within the blank window.
Define the name "Data_Range" ( without the quotes ! ) to encompass your data range , and run the procedure.
Narayan
SirJB7
Excel Rōnin
Hi, guitarman!
Back after a few days and just wondering what went wrong with the formula I provided you, if anything wrong. Give a look at the file of the below link which uses that unique formula in columns H:L for your data from A:E, as you wrote.
http://dl.dropbox.com/u/60558749/Reversing%20spreadsheet%20data%20%28for%20guitarman%20at%20chandoo.org%29.xlsx
Please let me know if it didn't work as you posted upwards so as to correct it.
Regards!
Back after a few days and just wondering what went wrong with the formula I provided you, if anything wrong. Give a look at the file of the below link which uses that unique formula in columns H:L for your data from A:E, as you wrote.
http://dl.dropbox.com/u/60558749/Reversing%20spreadsheet%20data%20%28for%20guitarman%20at%20chandoo.org%29.xlsx
Please let me know if it didn't work as you posted upwards so as to correct it.
Regards!