+ Reply to Thread
Results 1 to 6 of 6

Sorting the merge cells

  1. 08-03-2019, 01:45 AM #1
    Eric Tsang
    Eric Tsang is offline
    Forum Contributor
    Join Date
    12-06-2015
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    510

    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
    Reply With QuoteRegister To Reply
  2. 08-03-2019, 01:48 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,410

    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!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 08-03-2019, 01:53 AM #3
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,410

    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.
    Reply With QuoteRegister To Reply
  4. 08-03-2019, 02:01 AM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,410

    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
    Reply With QuoteRegister To Reply
  5. 08-03-2019, 02:38 AM #5
    Glenn Kennedy
    Glenn Kennedy is online now
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,984

    Re: Sorting the merge cells

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



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  6. 08-03-2019, 05:50 AM #6
    Eric Tsang
    Eric Tsang is offline
    Forum Contributor
    Join Date
    12-06-2015
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    510

    Re: Sorting the merge cells

    Dear Aligw

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

    Thanks
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Sorting a mail merge by a date
    By lochdara in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 12-30-2015, 12:43 PM
  2. Sorting data between un-merge and merge
    By kleptilian in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-12-2015, 04:28 PM
  3. MERGE CELLSHow to merge two cells in excel?
    By laure abbass in forum Excel General
    Replies: 1
    Last Post: 11-10-2005, 01:50 PM
  4. [SOLVED] Merge files & sorting data
    By GRAHAM MASON in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  5. Merge files & sorting data
    By Anne Troy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 07:05 AM
  6. Merge files & sorting data
    By GRAHAM MASON in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  7. [SOLVED] Merge files & sorting data
    By GRAHAM MASON in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1