How do you remove SORT from a column so that they all go back to their previous unsorted listing

[damian_r_Home]

Howdo.

We have a common spreadsheet in the office which has numerous columns with filters on it.

The problem is that somebody (and i cant find who) keeps adding a SORT ability to one of the columns and whatever i do to try and remove the SORT function the spreadsheet remains as it was when SORTed rather than going back to the original way it was all listed

I'm getting the horrible feeling that once sorted it may not even be possible to put them back to how they were listed and that |I might have to manually put some 450+ rows back to their original order manually - if this is the case, how can I stop people from using this sort function?

As usual, any help will be gratefully accepted.

Stay safe

D

 

[Yongle]

If you do not already use sheet protection
Select all cells in worksheet \ right click in any cell \ Format Cells \ Protection \ Unlock all cells

Right-click on sheet tab \ Protect Sheet \ then select all the options EXCEPT "Sort" (listed on 2nd page of options) \ apply a simple password and click ok

If sheet is already protected, then uncheck Sort" option and reapply protection

 

[damian_r_Home]

If you do not already use sheet protection
Select all cells in worksheet \ right click in any cell \ Format Cells \ Protection \ Unlock all cells

View attachment 8007
Right-click on sheet tab \ Protect Sheet \ then select all the options EXCEPT "Sort" (listed on 2nd page of options) \ apply a simple password and click ok

If sheet is already protected, then uncheck Sort" option and reapply protection

View attachment 8004

Thanks Yongle ...
Am I to assume that once somebody has sorted a column it cant be undone then?

 

[Yongle]

You can hit UNDO at the time but that is your only option

How was the original data arranged ?
- presumably it was in the sequence it was input

Is there anything in the data that can be used to tell Excel how to sort it ?
- may not be able to get it 100% correct but may be able to build a helper column to get it much closer and then you do the rest manually

The fact that you can do it manually suggests that there is a way to help
- if explain how you would tackle that (with some same data - use XL2BB) , perhaps I can help you further

 

[Peter_SSs]

may be able to build a helper column to get it much closer and then you do the rest manually

Another way that this might be able to be used is in your helper column, put a formula that refers to a sheet you could copy from a previous back-up ?

 

[damian_r_Home]

Hi Yongle.

The original row order that is required is indeed the input sequence and as such there inst any data that we can reset the spreadsheet by.

As for manually reordering the rows, i've got a backup i'm going to use.

Thanks for your help.

 

[Yongle]

In that case you must do the job manually
- but use @Peter_SSs suggestion to make life easier

As for the future your options include
1 sheet protection to prevent re-occurrence (see earlier post#2)
or
2 adding a column and number each item starting with 1 (and use custom format "0000" to harmonise the "look" to 0001, 0002 etc)
- this would allow the data to be sorted (if that is useful to someone) AND allow it to be put back in the original order
- suggest you prevent that column from being meddled with!!

 

[Peter_SSs]

i've got a backup i'm going to use.

So your helper column could still save a fair amount of manual work, if not all.

20 03 02.xlsm
	A
1	Data
2	a
3	c
4	d
5	e
6	b
Backup

20 03 02.xlsm
	A	B
1	Data
2	b	6
3	c	3
4	x	#N/A
5	a	2
6	d	4
Current

Cell Formulas
Range	Formula
B2:B6	B2	=MATCH(A2,Backup!A:A,0)

Now sot this sheet based on column B and then delete column B

 

[damian_r_Home]

Morning Folks.

Thanks for the follow up replies to my problem - soem useful bits there

 

[Peter_SSs]

Thanks for the follow up replies to my problem - soem useful bits there

Good luck with it. And I hope you eventually find the 'culprit'.