• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

unable to sort column from row 2 to end

mdavid

Member
Hi, I've attached an excel file that I'm unable to sort column from row 2 and keep the headings at the top - row 1.
Really appreciate if someone can sort this out.
 

Attachments

  • sort-problem.xlsm
    269.7 KB · Views: 4
Hi ,

I don't find any problem ; I selected the range A2:W1278 and did a sort without any problem. I ensured that the checkbox My data has headers was unchecked.

Narayan
 
Hi Narayan, thanks for checking.
I'm trying to sort 1 column from row 2 to end and I want to expand the selection. The sheet has a header so don't understand why you write
I ensured that the checkbox My data has headers was unchecked.

The 1st row is frozen, but when I sort from row 2 the first row becomes the first sorted row frozen, the header gets sorted with the rest of the column.
I've attached output from my sort - I've sorted column H from row 2 to end, the header is now located at row 1109.
 

Attachments

  • sort-problem-output.xlsm
    372.8 KB · Views: 3
mdavid
a) if You select ALL ROWS then My list has headers is as below
Screenshot 2019-11-14 at 21.30.50.png
b) if You select ROWS BELOW row #1 then My list has headers is unchecked

Select sort by Column as needed.
 
Hi tried both a and b above the result is the same as the attached sort-problem-output.xlsm attached to my last post, can you explain this?
 
mdavid
I also tried both ways ... without any challenges.
Next:
1) open Your original file (from #1)
2) start Marco recorder and record Your steps as You've done those.
After You have done BOTH ways
3) stop Macro recorder and send it here.
 
Hi, Hope this tells you/me something.
I also tried Open > open & repair > extract data > convert to values. The output from that I was able to sort successfully, but I understand I can lose data so would prefer another solution.
 

Attachments

  • sort-recorded.xlsm
    387.5 KB · Views: 2
Hi ,

This is the macro I recorded while doing a sort on column H within your data.

Just see if you can go through the same actions.

Narayan
 

Attachments

  • sort-problem.xlsm
    307.1 KB · Views: 1
Hi, The difference between what I and you are doing is that you're initially selecting the whole sheet which works ok.
I'm starting by selecting a single column, this doesn't work
 
Hi ,

Not at all.

The point is that I am selecting a range , you are selecting a whole column.

When you select a column , you are selecting a range , and the range is the whole column ; if you select column H , then the range is H1:H1048576.

And now if you uncheck the box My data contains headers , essentially you are telling Excel that your headers in row 1 are also a part of the data.

If you select the whole column , you have to check the box My data contains headers.

Narayan
 
mdavid
Based #7...
Do You would like to sort ONLY H-column?
> means, if You sort ONLY H-column ... then ONLY H-column will sort (and others will stay as those has been).

Next:
Create new excel-file with very basic data.
= two columns with some numbers and letter ... about 5 rows ... with headers.
Test different variations and learn, what will happen, if You do this-way or that-way.
In many cases, I would sort the whole sheet.
I'll select ALL columns and after that choose with which column will be a key and with which order.
After You have studied with basic data - test again with Your normal file.
 
What I'm doing is:

1. select 1 column
2. On Home tab > Sort & filter > Sort A - Z
3. In the Sort Warning box that pops up select Expand the sort > Sort

In the past this has always worked, but on the file I sent you it wasn't working - but on the last file you sent me it seems to have resolved itself - works
 
Hi vietm,
Appreciate your help, want to do things right and in future will sort by the method you recommend.
But this is not my 1st time sorting Excel sheets and "What I'm doing is:" above should also expand to the whole sheet and not include the headers if I select from row 2 to last row, but there's a problem with the sheet and the sort doesn't work as it should - no one's checked that.
 
mdavid
Eg. but there's a problem with the sheet and the sort doesn't work as it should - no one's checked that.
a) Yes - there's a challenge, but the sheet is Okay.

b) If You refer to this file then ... there were at least three views.
Screenshot 2019-11-15 at 19.55.45.png
... and if nobody else didn't find any challenges.

Why those three should comment something, if no comments back?
c) I rechecked that file again
... if You do as before then You have .. or have You noticed that 'Expand the Sort' EXPANDS to the WHOLE SHEET?
... ... including the 1st row - no matter have You selected from 2nd row or how ever.
That 'sorts' Your header (=1st row) to correct row as You have asked (maybe not wanted).

Please, try to be active to give answers,
those answers would help You to solve some of Your challenges.
... then others could know - what is going on there with that file?
 
Back
Top