Welcome Guest
Log In | Register )
You last visited December 7, 2016, 9:19 am
All times shown are
Eastern Time (GMT-5:00)

Excel Help Again

Topic closed. 8 replies. Last post 6 years ago by Sage.

Page 1 of 1
PrintE-mailLink
Sage's avatar - Lottery-035.jpg
Dunwoody Georgia
United States
Member #747
September 29, 2002
659 Posts
Offline
Posted: February 27, 2011, 5:19 pm - IP Logged

Good afternoon Lottery Post Family.  Last year I posted if someone could help me with an excel formula.  I wanted to take digits 0 through 9 and I want to put 5 digits in  cell A and have the formula show me the other 5 digits that was not in cell A show up in another cell like cell E or something?  I had a version with the macro and I would like a version without using macros if it's possible.  Thank you so much!   Smile

It's Never Too Late To Be The Person You Could Of Been!I Agree!

    B$Rizzle's avatar - a4leds
    The Ville, FL
    United States
    Member #95879
    August 19, 2010
    1708 Posts
    Offline
    Posted: February 27, 2011, 6:10 pm - IP Logged

    I need clarification.

     

    Do you want for example cells A1-A10 to be :

    A1: 0

    A2: 1

    A3: 2

    A4: 3

    A5: 4

    A6: 5

    A7: 6

    A8: 7

    And then in cell B1 show the #'s that are not  in cells A1-A10?  such as 8 & 9?

      Sage's avatar - Lottery-035.jpg
      Dunwoody Georgia
      United States
      Member #747
      September 29, 2002
      659 Posts
      Offline
      Posted: February 27, 2011, 6:13 pm - IP Logged

      I need clarification.

       

      Do you want for example cells A1-A10 to be :

      A1: 0

      A2: 1

      A3: 2

      A4: 3

      A5: 4

      A6: 5

      A7: 6

      A8: 7

      And then in cell B1 show the #'s that are not  in cells A1-A10?  such as 8 & 9?

      Thanks so much!  No I want all 5 digits to be in 1 cell 01234 in cell A or any cell and then show 56789 in another cell like cell E or something!  Thanks for a prompt response!

      It's Never Too Late To Be The Person You Could Of Been!I Agree!

        B$Rizzle's avatar - a4leds
        The Ville, FL
        United States
        Member #95879
        August 19, 2010
        1708 Posts
        Offline
        Posted: February 27, 2011, 6:51 pm - IP Logged

        I know of one way it will work but you have to use "conditional formatting".

         

        Also the numbers have to be entered into cells like this:

        For example in Column A1 put this:

        1-2-3-4-5

        2-3-4-5-6

        0-3-2-4-5

        1-2-3-4-5

        etc etc

         

        Then in column B you would have the #'s listed like this:

        0

        1

        2

        3

        4

        5

        6

        7

        8

        9

        If you use the forumula I can give you, it will hightlight all of the #s in Column B that are not in column A like this:

        The result would look like this:

         

        See how 7,8, & 9 are not in column A? They are highlighted because of the conditional formatting & formula I used.

         

        Let me know if this will work. If so I will explain further

          Sage's avatar - Lottery-035.jpg
          Dunwoody Georgia
          United States
          Member #747
          September 29, 2002
          659 Posts
          Offline
          Posted: February 27, 2011, 7:20 pm - IP Logged

          I know of one way it will work but you have to use "conditional formatting".

           

          Also the numbers have to be entered into cells like this:

          For example in Column A1 put this:

          1-2-3-4-5

          2-3-4-5-6

          0-3-2-4-5

          1-2-3-4-5

          etc etc

           

          Then in column B you would have the #'s listed like this:

          0

          1

          2

          3

          4

          5

          6

          7

          8

          9

          If you use the forumula I can give you, it will hightlight all of the #s in Column B that are not in column A like this:

          The result would look like this:

           

          See how 7,8, & 9 are not in column A? They are highlighted because of the conditional formatting & formula I used.

           

          Let me know if this will work. If so I will explain further

          I appreciate your help.  Yes this is exactly what I need.  Now is there a way to take the hightlighted numbers and put them in their own cell like cell C?  Also can you add the formula that hightlights them like this.  That is so awesome and I appreciate all your help!   Also I need this done without a Macro.  I have a macro version that goes crazy when I try to compile it in Doneex.

          It's Never Too Late To Be The Person You Could Of Been!I Agree!

            B$Rizzle's avatar - a4leds
            The Ville, FL
            United States
            Member #95879
            August 19, 2010
            1708 Posts
            Offline
            Posted: February 27, 2011, 8:24 pm - IP Logged

            Here is the formula:

            =COUNT(SEARCH("-"&B1&"-","-"&A$1:A$10&"-"))=0

             

            You have to set up the excel sheet first by adding the #'s in the specified column. In the example I used column A. Then in the next column you have to put the range of numbers you want. In the example I used 0-9.

             

            Once you do that highlight the #s in column B and then click on "Format" in Excel, and then "Conditional Formatting". Once you are there, under "Condition 1" select "Formula is" and type that formula in. Once you do that, in the same box click on format and add the formatting you want. In the example I provided, I just made the selections with a yellow background. You can customize it anyway you want.

             

            Also you may have to change the formula depending on which column/rows you are using. If you are using column A & B then the formula I provided will work

             

            I hope I explained it clearly. If you have any questions let me know.

             

            Not sure about having the results entered automatically into a different cell. Im sure its possible I just havent had a chance to work on it yet

              winsumloosesum's avatar - Lottery-060.jpg
              Pennsylvania
              United States
              Member #2218
              September 1, 2003
              5387 Posts
              Offline
              Posted: February 27, 2011, 9:13 pm - IP Logged

              Good afternoon Lottery Post Family.  Last year I posted if someone could help me with an excel formula.  I wanted to take digits 0 through 9 and I want to put 5 digits in  cell A and have the formula show me the other 5 digits that was not in cell A show up in another cell like cell E or something?  I had a version with the macro and I would like a version without using macros if it's possible.  Thank you so much!   Smile

              This is a method using user defined cell ranges. 

              http://www.box.net/shared/pb82nb5lmr

              Enter your 5 digit number in cell A1 (ascending order) and the missing 5 numbers will be in cell b1.

                B$Rizzle's avatar - a4leds
                The Ville, FL
                United States
                Member #95879
                August 19, 2010
                1708 Posts
                Offline
                Posted: February 27, 2011, 9:30 pm - IP Logged

                This is a method using user defined cell ranges. 

                http://www.box.net/shared/pb82nb5lmr

                Enter your 5 digit number in cell A1 (ascending order) and the missing 5 numbers will be in cell b1.

                There ya go Sage, what winsum has posted is exactly what you are wanting. Thumbs Up

                  Sage's avatar - Lottery-035.jpg
                  Dunwoody Georgia
                  United States
                  Member #747
                  September 29, 2002
                  659 Posts
                  Offline
                  Posted: February 28, 2011, 12:09 am - IP Logged

                  This is a method using user defined cell ranges. 

                  http://www.box.net/shared/pb82nb5lmr

                  Enter your 5 digit number in cell A1 (ascending order) and the missing 5 numbers will be in cell b1.

                  Lol Thank you so much!  Both of you guys for helping me!  It's so appreciated!   Thumbs UpI Agree!Smile

                  It's Never Too Late To Be The Person You Could Of Been!I Agree!