+ Reply to Thread
Results 1 to 8 of 8

Formula to alternate two values between rooms

  1. #1
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2010
    Posts
    157

    Formula to alternate two values between rooms

    I get a file with a list of rooms. Some of the rooms repeat and others do not. It varies every time the file is generated. It looks similar to below:
    RM 1
    RM 1
    RM 1
    RM 2
    RM 3
    RM 3
    RM 4
    RM 5
    RM 6
    RM 6
    RM 7

    My goal is to put an "A" next to the first room and then a "B" next to the subsequent room and then back to an "A" alternating between the two letters. I want it to look like this:
    RM 1 | A
    RM 1 | A
    RM 1 | A
    RM 2 | B
    RM 3 | A
    RM 3 | A
    RM 4 | B
    RM 5 | A
    RM 6 | B
    RM 6 | B
    RM 7 | A

    What's the best way to do this in a singular formula?
    Last edited by Ricker090; 09-11-2017 at 06:12 PM. Reason: Solved

  2. #2
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Formula to alternate two values between rooms

    Assuming your data is in Column A you could try

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Please do add reputation where you see fit, it's nice to be nice and we all enjoy a pat on the back

    Please also mark your thread as solved once it has been.

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Formula to alternate two values between rooms

    Try this
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Or this
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    in B1 and copy down
    v A B
    1 RM 1 A
    2 RM 1 A
    3 RM 1 A
    4 RM 2 B
    5 RM 3 A
    6 RM 3 A
    7 RM 4 B
    8 RM 5 A
    9 RM 6 B
    10 RM 6 B
    11 RM 7 A
    Last edited by AlKey; 09-01-2017 at 11:22 AM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: Formula to alternate two values between rooms

    I apologize. I forgot to mention that the room names will also change. Relying the odd/even value could yield some undesirable results. For example, immediately following RM 7 in the above data, the next row could be CL 1. The function needs to be able to determine if it's truly a different class or not.

    One good thing is that the data is grouped, so all the RM 1's will be together and so on and so forth.

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Formula to alternate two values between rooms

    Quote Originally Posted by Ricker090 View Post
    I apologize. I forgot to mention that the room names will also change. Relying the odd/even value could yield some undesirable results. For example, immediately following RM 7 in the above data, the next row could be CL 1. The function needs to be able to determine if it's truly a different class or not.

    One good thing is that the data is grouped, so all the RM 1's will be together and so on and so forth.
    If the names are always two letters word use the second formula in my post.

  6. #6
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: Formula to alternate two values between rooms

    Unfortunately, when I apply the second formula, it still gives the same results.

    I want the column next to CM 1 to show "B" as it's value.

    Capture.PNG

  7. #7
    Valued Forum Contributor PFDave's Avatar
    Join Date
    05-17-2012
    Location
    Milton Keynes, England
    MS-Off Ver
    Excel 2013
    Posts
    1,067

    Re: Formula to alternate two values between rooms

    start with A in cell B1 then from B2 type:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    The only issue I can envisage is that if the Rooms have to be in some sort of order in column A but I can't establish everything from your OP, hence why myself and ALKEY provided the solutions that we did.

  8. #8
    Forum Contributor
    Join Date
    06-17-2010
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2010
    Posts
    157

    Re: Formula to alternate two values between rooms

    Sorry for the late reply, but that last formula did it. The only thing I was missing was assigning the first record a letter.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Alternate Coloring of Common Values
    By NFJimc in forum Excel General
    Replies: 1
    Last Post: 03-23-2017, 03:18 PM
  2. Choosing Rooms
    By realvirtuality1 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-18-2014, 05:20 PM
  3. [SOLVED] Search for free rooms
    By Berna11 in forum Excel General
    Replies: 4
    Last Post: 02-16-2014, 05:33 AM
  4. Percentage Formula for rooms completed
    By tf4string in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-19-2013, 07:38 PM
  5. Formula: Alternate cell values
    By ugsquish in forum Excel General
    Replies: 2
    Last Post: 09-09-2011, 04:41 PM
  6. [SOLVED] rooms checklist
    By hany elkader in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-25-2006, 01:15 AM
  7. [SOLVED] Finding values within text and substituting with alternate values.
    By Bhupinder Rayat in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-24-2006, 09:45 AM

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