+ Reply to Thread
Results 1 to 6 of 6

Sorting the merge cells

  1. #1
    Forum Contributor
    Join Date
    12-06-2015
    Location
    Hong Kong
    MS-Off Ver
    2010
    Posts
    300

    Sorting the merge cells

    Dear all

    I am working on a rooming list where we have to indicate the single room, twinbed room, doublebed room

    Single Room - 1 guest
    twin bed room - 1 or 2 guests, separated bed
    double bed - 1 or 2 guests, one king bed

    I have all the guests names and assigned to different room type.
    I have to merge the cell for the room to indicate that they are assigned in same room

    Issues are when i want to
    a) count the rooms, sometime it does not work for merged cell

    b)sort the arrival date, it does not work

    See attached a sample.

    How can i solve it

    appreciated your help in advance


    Eric

    i
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    30,365

    Re: Sorting the merge cells

    You should NOT use merged cells. They will cause you all sorts of problems going forward. You do not need them: they are not essential to your project. They might look nice, but they are a wolf in sheep's clothing. Get rid of them - lay your data out differently.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    30,365

    Re: Sorting the merge cells

    Here's what I would recommend:

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    2
    Name Room
    Room No.
    In Out
    3
    Paul Single
    1
    03-Aug
    07-Aug
    4
    Mary Single
    2
    01-Aug
    03-Aug
    5
    Eric Twin
    1
    10-Aug
    12-Aug
    6
    Sam Twin
    1
    10-Aug
    12-Aug
    7
    Sue Double
    1
    09-Aug
    12-Aug
    8
    Sou Double
    1
    09-Aug
    12-Aug
    9
    Susan Double
    2
    08-Aug
    10-Aug
    10
    Peter Double
    2
    08-Aug
    10-Aug
    11
    12
    13
    14
    Room Count
    15
    Single
    2
    16
    Twin
    1
    17
    Double
    2
    Sheet: Room_Sorting

    In B15:

    =MAX(IF($B$3:$B$10=A15,$C$3:$C$10))

    ... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    30,365

    Re: Sorting the merge cells

    Turn your data range into a table and you can sort it however you wish:

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    2
    Name Room
    Room No.
    In Out
    3
    Mary Single
    2
    01-Aug
    03-Aug
    4
    Paul Single
    1
    03-Aug
    07-Aug
    5
    Susan Double
    2
    08-Aug
    10-Aug
    6
    Peter Double
    2
    08-Aug
    10-Aug
    7
    Sue Double
    1
    09-Aug
    12-Aug
    8
    Sou Double
    1
    09-Aug
    12-Aug
    9
    Eric Twin
    1
    10-Aug
    12-Aug
    10
    Sam Twin
    1
    10-Aug
    12-Aug
    Sheet: Room_Sorting
    Attached Files Attached Files

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    21,558

    Re: Sorting the merge cells

    Agreed. Merged cells are a disaster in the body of the data. NEVER use them.
    Glenn



  6. #6
    Forum Contributor
    Join Date
    12-06-2015
    Location
    Hong Kong
    MS-Off Ver
    2010
    Posts
    300

    Re: Sorting the merge cells

    Dear Aligw

    The solution is what i have been think for a long long time.

    Thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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