Custom Sorting via VBA

[REUBEN]

Hi All,

I need to sort three columns on a condition that the First column 'B', is sorted in alphabetical ascending order, then column 'C' is sorted based on the texts is contains and finally column 'D' is sorted with the texts it contains.

Background on the sheet (only FYI), column 'B' is the name of customers, column 'C' is the service we provided them and column 'D' is the specific task we did for them within the respective service. And the options in column 'B' and 'D' are provided via a drop down menu. So the text phrases/options remains the same.

I am unable to script anything in VBA and I understand the above would need to be coded in to VBA.

Thank you for helping me out.

 

[footoo]

You can create a macro to do this with the macro recorder.

Turn on the recorder, select all the columns containing data, go to Data/Sort, sort by col B then by col C then by col D, turn off the recorder.

 

[REUBEN]

You can create a macro to do this with the macro recorder.

Turn on the recorder, select all the columns containing data, go to Data/Sort, sort by col B then by col C then by col D, turn off the recorder.

Thank you for your quick reply.

One detail I forgot to mention was that the last row contains text that must not be changed with. And this text is auto populated everytime a user has completed one full row of data.

The Sort function must not sort this last row.

Anywya, I tried the macro recorder just now (its a great tool to automate stuff). But after I sorted the second column 'C', the data that was storted in the first column 'C', was shuffled as well.

My idea is to have data sorted primarily based on Column 'B' (name of customers), then by column 'C' (the services provided to each customer) and finally by column 'D', (individual tasks performed under each service). So the end result will be a display to see bunched entries for each customer arranged alphabetically, and the service & their corresponding tasks also sorted accordingly.

Hope this makes sense.

 

[footoo]

Anywya, I tried the macro recorder just now (its a great tool to automate stuff). But after I sorted the second column 'C', the data that was storted in the first column 'C', was shuffled as well.

You did not sort properly.
You need to sort by three levels - select sort by col B, add level & select sort by col C, add level and sort by col D, click OK.

To set the sort range, what column can be used to find the last row?

 

[Peter_SSs]

Does the data actually start in column A? If so, should column A be shuffled when the data is sorted on columns B:D?

Is there data beyond column D? If so, should that data also be shuffled when the data is sorted on columns B:D?

 

[REUBEN]

You did not sort properly.
You need to sort by three levels - select sort by col B, add level & select sort by col C, add level and sort by col D, click OK.

To set the sort range, what column can be used to find the last row?

Tried it out again and if I just need to sort the 3 columns, then the procedure you've described works perfectly. However, that takes in the last row as well and sorts it as per alphabetical order. I need to prevent excel from doing this. As that last row contains auto-populated instructions for the user when they start adding data to the following row/s.

Any suggestions how to prevent excel from sorting the last row?

 

[REUBEN]

Does the data actually start in column A? If so, should column A be shuffled when the data is sorted on columns B:D?

Is there data beyond column D? If so, should that data also be shuffled when the data is sorted on columns B:D?

Hi Peter_SSs

Good guess, but the first column is an empty one. I'm using it to add a sort of padding to the first column where the data begins (for cosmetic reasons). There is data beyond column 'D', and I believe, the above described Data Sort process (by footoo) would be taking care of this.

 

[Peter_SSs]

Any suggestions how to prevent excel from sorting the last row?

Yes, but it depends on the answers to my previous questions.

Edit: OK, we posted at about the same time.
Still, can you confirm that the data beyond column D gets rearranged, along with the B:D data?

 

[REUBEN]

Are you referring to whether the data in the following columns must be shuffled as well? Then Yes, they too must be shuffled accodingly.

 

[footoo]

You need to answer this question :

To set the sort range, what column can be used to find the last row?