+ Reply to Thread
Results 1 to 2 of 2

Rows dis-aligning after sort

  1. #1
    Registered User
    Join Date
    MS-Off Ver

    Rows dis-aligning after sort

    Thanks in advance for all the help. I'm not very well versed in Excel so I have no clue if this is an easy fix or not.

    I created a sheet which ended of having merged cells (most of the sheet is pasted in) so when I attempted to sort I got the message

    'To do this, all merged cells need to be the same size."

    "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."

    I un-merged all cells all cell hoping to clear the error, but now when I sort all of the rows dis-align. See attached pictures for before and after attempted sort.
    Attached Images Attached Images

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    Rocester, New York, USA
    MS-Off Ver
    Excel 2007:10

    Re: Rows dis-aligning after sort

    Two things going on here.

    1) Because column H is empty, the Sort function is considering stuff in A1:G20 as a separate table compared to H1:H20.
    Solution: delete column H so it's contiguous data.

    2) Column A is offset compared to columns B:H; all the data is in row 2, whereas it's in row 1 in the other columns. Because sort works by row, it's sorting all the rows with stuff in column A at the top, and then all the rows where A is blank at the bottom.
    a) grab A2:A20, and move the selected range up one cell, so it's in-line with the data in B2:H20.
    b) highlight the whole table and click Data Ribbon => Data Tools Pane => Remove Duplicates Button. That will launch a dialogue box you can use to delete duplicates -- and the all-empty rows will be duplicates of each other.

    Going forward, as a rule of thumb, Merging Cells is by default a bad idea. Try to avoid doing it.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1