# Solved: Excel sorting problem



## fahad_m85 (Apr 3, 2009)

dear friends
when i sort data in my excel 2007 sheet it give a message

"The cell range that you attempted to sort or remove duplicates from includes merged cells. However not all cells in the range are merged to the same size.
To complete this operation, unmerge all the merged cells in the range, or make each group of merged cells the same size as the largest group."

what can i do in this situtaion


----------



## The Villan (Feb 20, 2006)

Upload a version of it on this thread then we can see what the problem is. No sensitive data though.


----------



## fahad_m85 (Apr 3, 2009)

please find the attached file
there is also another problem when i protect my sheet and also check the option
"sort" 
in 
"Allow all users of this worksheet to:" 
window it dont sort the data 
why it is?


----------



## The Villan (Feb 20, 2006)

I have had a look at you spreadsheet. By merging cells you are creating problems for Excel.

To sort data or use data filter, you must not have blank rows or columns.

By merging for example column C & D you are making a big mistake. 

You must have a field heading in its own cell so Doc Type is in C6 and D6 is blank. You have merged that. What you need to do is make a spreadsheet where you only have headings using on ecell each and not having these blank cells in between. You can wrap text in a cell, but dont merge 2 cells.

So
A6 DATE
B6 DOC TYPE
C6 NARRATION
D6 KHJ
E6 MSH
F6 OTC
G6 DEBIT
H6 CREDIT
I6 BALANCE

You then put your data underneath that. However your Balance column should not be included in the sort.

You have unfortunately designed this spreadsheet as though it was a word processing document and made it nice and fancy, but taken away all the functionality of the spreadsheet.

There are rules to be adhered to if you are going to avoid pitfalls as you are experiencing.

Unfortunately I have an appointment shortly and will not be back for about 3 hours or so.

I am quite willing to work with you on good spreadsheet design when I get back if that is OK

Cheers
Les


----------



## The Villan (Feb 20, 2006)

Incidentally, you should always create your data area with data and formulas first and get that right and then worry about the headings and formatting etc last.

That is a tip based on experience, so you are free to take that on board or not. :up:


----------



## fahad_m85 (Apr 3, 2009)

dear due to unavoidable it is neccessary for me to create my ledger like this because i dont have only one ledger it is only the sample i have about 120 sheets like this
so that when i create a new ledger of a party i only give the party name in it then all fields create automatically due to formulas like party address, contact no. file no. ledger no. etc
so if i follow your instruction then there is no much space for fields specially party address and contact no. the data is hide in the cells or if i increase the column width then the size of sheet is distrubed and it is not look very well on A4 page and the image of company and specially my image is fallen
so have you any solution for my sheets


----------



## The Villan (Feb 20, 2006)

IMHO you should be using Access Database or an Accounting Package. You then produce reports from that.

However, you have chosen the Excel route,so that doesn't help at the moment.

I am not sure I can do anything, as you have stated, it needs to remain the way it is.

Let me ask some further questions

Why do you need to sort it, if it is in Invoice number order?


----------



## The Villan (Feb 20, 2006)

Another question

What area of the spreadsheet are you trying to sort?

Is it all columns including the balances?


----------



## fahad_m85 (Apr 3, 2009)

Dear i dont want to sort balance i just want to sort dates because they are in desending order
so when i need to dispatch a ledger to a party i want that dates are in asending order so this is my need


----------



## The Villan (Feb 20, 2006)

OK I have been doing some interrogation of your spreadsheet.

There is a way round this, by using a macro that unmerges all cells below the merged headings
Then sort the data from the Date column to the Credit column
Then merge the cells back.

This could be created as a global macro that would work in each file.

I am not into Macro workanymore, but ther are people on this forum who are real smokey with it.

If you would like somebody to develope this with Macro's, please indicate YES in your next post.


----------



## The Villan (Feb 20, 2006)

I am assuming this is in Excel 2007

Your current example that needs to be sorted is from B7 to L500 which includes column headings from DATE to CREDIT and the sorting excludes the headings.

The maco needs to (in this example) be done something like this

Doc Type
Select C7 to C500 and unmerge the cells (it does not include the headings) so when unmerged you then have C7 to D500 which will need to be merged across when the sort has been done.

Narration
Select E7 to E500 and unmerge the cells (it does not include the headings) so when unmerged you then have E7 to F500 which will need to be merged across when the sort has been done.

Debit
Select J7 to J500 and unmerge the cells (it does not include the headings) so when unmerged you then have J7 to K500 which will need to be merged across when the sort has been done.

Credit
Select L7 to L500 and unmerge the cells (it does not include the headings) so when unmerged you then have L7 to N500 (note 3 cells across) which will need to be merged across when the sort has been done.

B7 to N500 or just the records as in this example B7 to N9 need to be sorted as per requirement.

The cells need to be merged again

Doc Type
Select C7 to D500 and "merge across" the cells (it does not include the headings) so when merged you then have C7 to C500

Narration
Select E7 to F500 and "merge across" the cells (it does not include the headings) so when merged you then have E7 to E500

Debit
Select J7 to K500 and "merge across" the cells (it does not include the headings) so when merged you then have J7 to J500

Credit
Select L7 to N500 and "merge across the cells (it does not include the headings) so when merged you then have L7 to L500 (note 3 cells across)

Thats it for just the sorting. Obviously questions need to be asked to see if this example is standard to all the workbooks.

My assumption would be that this would be a global macro that would take all control away from the user whilst it is being done and control being given back to the user afterwards.

I hope that helps for anybody wanting to take on this macro work.


----------



## fahad_m85 (Apr 3, 2009)

yes please reffer me thet who will make this macro for me


----------



## The Villan (Feb 20, 2006)

OK Give it a little time and hopefully somebody will be along who can help you further.

Fancy Pakistan allowing Australia to win. Now England have to play them in the semi final. Pakistan should beat New Zealand in the other semi final.


----------



## Zack Barresse (Jul 25, 2004)

My advice: unmerge the cells. Period. Deal with the formatting. Merged cells are quite possibly the absolute worst invention ever added into Excel. If you're wanting a header of other sorts, you should be using a text box of sorts. Writing a macro to undo all of the merged cells, perform the action, then re-merge is ludacris.


----------



## The Villan (Feb 20, 2006)

I agree Zack, and have already posed that to Fahad. However, I do not think he is in a position to change things.

It all boils down to, can anybody do the macro or not.

If I had been looking at this 14 years ago, I would have managed it no problem, but today I have no need to write macros anymore or the desire.

I guess Fahad is left with two choices - change, as you and I suggest or just live with the fact he can't sort it, if nobody will write the maacro.


----------



## Zack Barresse (Jul 25, 2004)

Will the last row always be row 500?


----------



## fahad_m85 (Apr 3, 2009)

dear friends in have no much time to unmerge cells and then merge them again because i m supervisor in my company so i have lot of work.
my boss say some ledgers then i g ive him soon without wasting time so there is no time to reformating it.
the reason of sorting dates is to make them in sequence because some time my boss said why the 1-10-2009 comes before 28-9-2009 then i explain him that the entry of that date is came late then my boss said "you should sort dates in assending order" but he dont know about the excel limitations and no much knowledge about excel and this is my problem.
but you are right about reformating if i have some ledgers but i told later that i have approx 120 ledgers so how it possible if i want to print 10 ledgers in a day while i have many other works like inventory management, accounts management, voucher entries, invoicing, ledger entries, database entries, and many other works.
i think you understand my problem
i think there is an other way that i change the whole ledger formating at once and make it standard for others
so can any one make the sample ledger for me but it should near by about my ledger all fields in my ledger is necessary so make a sample ledger and attach it. it is quit easier then to write a macro
if any ledger pass then i make the all ledgers like it at once it is hard work for me but i give the overtime to company if my problem is solve.


----------



## The Villan (Feb 20, 2006)

I understand all of that Fahad. Your company is probably very typical.

What is important, is to answer questions asked.

Zack asked if each ledger will always have 500 rows or does it vary per customer?


----------



## slurpee55 (Oct 20, 2004)

Zack, could you write code that would, say, insert a new row or two, copy all the data in the merged cells and then paste that data into the new row(s)? If all the sheets were changed like that, then you could address the sorting problem.


----------



## The Villan (Feb 20, 2006)

I have just manually adjusted you spreadsheet to still keep it looking the way you had it before, but without having merged cells.
With this scenario you can now sort the data.
The only thing is that I have not protected the spreadsheet.
As an exercise can you see much difference to what you had before. Does it still look and feel the same?
Are the formulas doing what they are supposed to?


----------



## fahad_m85 (Apr 3, 2009)

dear villian i seen your edited ledger it looks better but their are still some problems.
these are the same problems which i faced when i started making ledger for that reason i merger many cells with each other and lose the sorting facility
1 in your ledger the contact no. "E3" is not show the complete text and also the no. like 03214358005 because the cell width is decreased and same as city "E2" like "muzaffarabad"
2 the intent of remarks column is dead because when i want to give a remarks against an entry the the balance status " cr or dr or bl" is deleted and the whole ledger is dismissed 
And the question of zack about 500 rows
this is my safe side to formating 500 rows per costumers i dont print the whole ledger i print only that area where the data is. i thinked 500 rows is vary for 2 years ledger but now i think after 2 years there is big problem created for me but i find the solution for this.
the problem for me in current situtaion is only sorting


----------



## The Villan (Feb 20, 2006)

Have a look at this one

I have added a couple of lines above the headings and moved the data around a little.

I inserted an extra column for the DR/CR/BL
You can now add comments seperately

I have external links turned oof as I dont have thos e spreadsheets, so not all the external dat will have updated.

That will only happen when the design is OKed and soembody can then do the macro work to get you up and running.

Commenst back on the new version please.


----------



## fahad_m85 (Apr 3, 2009)

dear villian i have look on your edited one 
dear if i want only sort data then i also make a sheet like this but i want to sorting, balance printing, and also a fancy look.
dear i dont want to let my costumers to guess that i make the ledgers manually on excel due to the good image of my company so my preference is the good looking ledger on paper then balance printing and sorting is the last preference
BUT when i watching your sample ledger i got an idea and edited it and last i success to reach my requirements i upload the final ledger you should it and thankyou very much to take interest in my problem thankyou once again
but now there is another problem when i sort a protected sheet it cannot sort it and give a message please check it villian i protect it the password is sample 
i also chect the box "sort"
but it not sorting it


----------



## The Villan (Feb 20, 2006)

Fahad

I have locked the field headings B9 to I9 Not all of them were locked.

You didn't have a formula in J10, which I have done

You have to select the data you want to sort first B10 to I17 in your example.

Then if you click on A-Z or Z-A it will sort.

I have protected the WS without a password for ease at the moment.

By the way, you do not need to use *dear* in your posts on this forum.


----------



## fahad_m85 (Apr 3, 2009)

villian i do it but it cannot sort it is giving me a message
*"the cell or chart you are trying to change is proteced and therefore read-only
to modify a protected cell or chart, first remove protection using ...*"
after all i using ms office 2007
is this a version problem


----------



## The Villan (Feb 20, 2006)

It works for me and I am using Excel 2007

Can you do the following

Save the file I uploaded to your desktop and open it from theer and try again.

remember to select only the following cells B10 to I17

Then report back


----------



## zaxbat (Oct 6, 2009)

Access makes Excel pretty much obsolete. Just put it all into Access.


----------



## The Villan (Feb 20, 2006)

Zaxbat
That has been discussed already and I agree with you. However It has to be in Excel as has already been discussed in previous posts.


----------



## fahad_m85 (Apr 3, 2009)

i followed your instructions but it remain giving me the same error 
data is filtered but cant sorted why it is? while i also check the option "sort" in "protect sheet popup"

many friends tell me to manage your accounts and inventory control system in access i also want to do this but i never work on access can you tell me how it is?


----------



## The Villan (Feb 20, 2006)

I really do not understand why it doesn't work for you.

When it is protected have you got Select unlocked cells ticked as well as sort and use autofilter ?

Ah so you want to use Access now. Good. You will get more than enough help to get that set up for you. Do you have the time to learn and do your job?


----------



## The Villan (Feb 20, 2006)

Ok another thought

Open you own spreadsheet that you altered yourself.

Turn the protection off

Turn off filter

Lock the headings

Select all the cells from B10 to I503 and take the ticks off Locked and hidden in the Protection tab

When you have done that, select the field headings and turn the filter on

Click on B10 and then protect the sheet without using the password for the moment. You only need to do that when you have sorted out the issues.

Then select B10 to I17 and sort

Does that work then?


----------



## The Villan (Feb 20, 2006)

If That doesn't work, I am going to ask you to attach your workbook to an e-mail and send it to me.
I will have a look at it and see if it works or not on my computer.


----------



## fahad_m85 (Apr 3, 2009)

villian i can select ticked as well as sort and autofilter but when i click "sort oldest to newest" sheet flash once and give the error i also note that when i unchecked the option "use auto filter" in "protect sheet popup" i unable to filter data and then it give the same error but when i checked it i can filter my data but not working on sort option i amazed why it is i think it is instalation problem because i download ms office 2007 from internet

villian i want to learn access as good as i know excel i know it is difficult to learn it by forum but if you teach me how to start making accounts database and other inventor controll system in access then i get the way to explore the access slowly slowly


----------



## The Villan (Feb 20, 2006)

Fahad
When I select the data to sort, I just click on the buttons A-Z or Z-A and it works fine.

you have my e-mail address send me the file

I am just looking up 2 very cheap books that are absolutely brilliant for learning how to use Access. I used them for myself and for a few people who wanted to learn.
I will post the link when I have found it.


----------



## fahad_m85 (Apr 3, 2009)

villian it works when i select b10 to L503 and unchecked the hidden and locked options in pretection tab
but if i locked the cell J10 TO K503 IT DONT WORKS AND GIVING THE SAME ERROR


----------



## The Villan (Feb 20, 2006)

This is the book I mean. They do brilliant easy to use illustrated examples that you can prectice and practice. IMHO the best on the market for people who want to learn quickly with the minmum of fuss.

http://www.amazon.co.uk/gp/reader/1905292368/ref=sib_dp_ptu#reader-page

This is the follow on from the other book, but am not sure if it out yet for 2007

http://www.amazon.co.uk/gp/reader/1904467741/ref=sib_dp_pt#reader-link

However the first one will really get you up and running and you will enjoy learning with that book


----------



## The Villan (Feb 20, 2006)

>>but if i locked the cell J10 TO K503 IT DONT WORKS AND GIVING THE SAME ERROR <<

That makes sense

J10 to K503 shouldn't be locked.

You cant lock part of the data you want to sort


----------



## The Villan (Feb 20, 2006)

When you protect a worksheet and allow sorting, you can only sort blocks of data that are unlocked. There must not be any cells locked within the range you select to sort


----------



## fahad_m85 (Apr 3, 2009)

yes you are right but there are formulas in balance column and if i dont locked it may be it will edited or changed by me or someone so it must be locked but if i want to sort then i will need to unprotect sheet 
my workbook contains old style ledgers you seen before but now i am starting to change all ledgers in new designed ledger


----------



## fahad_m85 (Apr 3, 2009)

thankyou villian for the books i get it 
i think it cant downloadable i will read it on net only
thankyou very much again i marked this thread as a solved give me your personal email address *if you dont mind* so that i will in touch with you
thanks


----------



## The Villan (Feb 20, 2006)

Oh I am sorry Fahad, must be getting tired 

You should have J and K locked.

But you should only select from B10 to i503 not L503

Thats why it wont sort becuase you have included locked cells.

Columns b, C,D, E, F, G, H I (i) are unlocked
Columns J, K L are locked cells (you must not select that range for sorting.)


----------



## The Villan (Feb 20, 2006)

Aaaah yes the problem is that your remarks column needs to be sorted as well which is column L

For column L to sort as well, it needs to be before the Balance column and unlocked.

You cannot have a locked column in between.

Maybe you could put you remarks column next to your Narration Column, so that the Balance is to the right of the data you select for sorting.
That way you can lock the formula cells and sort.


----------



## The Villan (Feb 20, 2006)

Th ebook you need to buy, you can't read it on the internet. They just show you a few pages so that you can see what the book lokks like and also see what topics they cover.

The book is not that expensive and would be well worth buying so that you have it with you all the time.


----------



## The Villan (Feb 20, 2006)

Here is an example

OOps didn't put the filter on


----------



## fahad_m85 (Apr 3, 2009)

villian you are right this ebooks shows only 8 pages 
its price is no too much but it is costly for me because 10.62 x 122.9 = 1305/- pakistani rupees while my salary is 10000/- per month
so have you another link for free ebook
or if you dont mind did you purchase this book and can you give me please *dont mind*


----------



## The Villan (Feb 20, 2006)

hee hee. I don't earn anything or have any allowances, so I am worse off than you. 

Well why don't you start from here by using Youtube lessons.






You will see lots of lessons on the right hand side.


----------



## fahad_m85 (Apr 3, 2009)

*villian i start a new thread in business application name access learning please check it
*
"friends i doing my work on excel but some friends told me to do your work on access because access is best for this type of work
i export it into access now i am facing some problems i upload the access database
you see it and find the solution of problems
first problem is:
in excel i create a report through formulas the reports contains the party names and shows how much cartons or pieces dispatched to him, how much cartons or pieces returned from him and how much his sale. the reports shows the dispatch, returned, and sale in unit wise and product wise. now i want to make a same report in access is this possible or not ? 
i also upload a excel report but the formulas were deleted because they needed the linked workbook"


----------

