1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel sorting problem

Discussion in 'Business Applications' started by fahad_m85, Sep 26, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. fahad_m85

    fahad_m85 Thread Starter

    Joined:
    Apr 3, 2009
    Messages:
    129
    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
     
  2. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,239
    Upload a version of it on this thread then we can see what the problem is. No sensitive data though.
     
  3. fahad_m85

    fahad_m85 Thread Starter

    Joined:
    Apr 3, 2009
    Messages:
    129
    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?
     

    Attached Files:

  4. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,239
    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
     
  5. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,239
    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. :)(y)
     
  6. fahad_m85

    fahad_m85 Thread Starter

    Joined:
    Apr 3, 2009
    Messages:
    129
    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
     
  7. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,239
    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?
     
  8. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,239
    Another question

    What area of the spreadsheet are you trying to sort?

    Is it all columns including the balances?
     
  9. fahad_m85

    fahad_m85 Thread Starter

    Joined:
    Apr 3, 2009
    Messages:
    129
    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
     
  10. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,239
    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.
     
  11. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,239
    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.
     
  12. fahad_m85

    fahad_m85 Thread Starter

    Joined:
    Apr 3, 2009
    Messages:
    129
    yes please reffer me thet who will make this macro for me
     
  13. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,239
    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.
     
  14. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    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.
     
  15. The Villan

    The Villan

    Joined:
    Feb 20, 2006
    Messages:
    2,239
    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.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/863885